The first set of operators we’ll look at are the basic operators, or the ones that use symbols instead of keywords.

Equal To

To determine if something is equal to something else, we use the equals sign =.

This is often used in the WHERE clause to find records that match a certain value.

WHERE expression = expression

For example, to find all of the customers in Florida, our query may look like this:

SELECT id, first_name, address_state
FROM customer
WHERE address_state = 'FL';

We want to see records where the address_state value is equal to ‘FL’.

We use single quotes around FL as it is a string or text value, which need single quotes. Without the single quotes, it would be treated as an SQL keyword and likely show an error.

(If you’re using a value that has single quotes in it, such as “O’Reilly”, you’ll need to “escape” the single quotes. You can learn what that is and how to do that in this article.)

We can also use this with numbers:

SELECT id, first_name, address_state
FROM customer
WHERE id = 4;

Or with functions:

SELECT id, first_name, address_state
FROM customer
WHERE signup_date = TO_DATE('2018-01-04', 'YYYY-MM-DD');

It’s also often used in joins:

SELECT
c.id,
c.first_name,
c.last_name,
c.address_state,
o.order_id,
i.order_date
FROM customer c
INNER JOIN order o ON c.id = o.customer_id;

In this example, the join is performed between the customer and order table, where the order’s customer ID is equal to the customer’s ID.

In almost every query you write with joins, you’ll use the equals sign to join tables. I’ve seen and written a few that use other symbols, but not very often.

Not Equal To

The concept of “not equal to” means that one expression is not equal to another expression. It’s the opposite of “equal to”.

In SQL, it can be represented in two ways.

• Using !=, which uses an exclamation point and is a common way of writing “not” in programming languages.
• Using <>, which is a combination of greater than and less than. Together, these symbols mean not equal to.

It could look like this:

WHERE expression != expression
WHERE expression <> expression

Which one should you use?

I would recommend using the two bracket version <>, because it’s ANSI-compliant, which means it’s part of the SQL standard. This means your query is more likely to be compatible with different databases, and it’s one less thing you need to change if you move databases.

Now, let’s say you wanted to find all customers who were not in the state of California (CA for short).

Your query may look like this:

SELECT id, first_name, address_state
FROM customer
WHERE address_state != 'CA';

It could also look like this:

SELECT id, first_name, address_state
FROM customer
WHERE address_state <> 'CA';

This can also be used with numbers.

SELECT id, first_name, address_state
FROM customer
WHERE id <> 3;

It can also be used with functions:

SELECT id, first_name, address_state
FROM customer
WHERE LENGTH(first_name) <> 3;

A not equals operator can be used in a join. For example, it could be used in a self join to get a list of all combinations of values, excluding those that match.

For example, if you had a sports database and wanted to create a list of home team and away team combinations:

SELECT
h.team_name AS home_team,
a.team_name AS away_team
FROM teams h
INNER JOIN teams a ON h.id <> a.id;

This would show a list of all team names in both columns where the ID does not match.

Greater Than

Another operator that can be used is the greater than operator.

This uses the angled bracket >. I remember it as the arrow always points to the smaller of the numbers: 5 > 3.

WHERE expression > expression

We can use it in queries on numbers:

SELECT id, first_name, address_state
FROM customer
WHERE id > 7;

We can also use it on dates:

SELECT order_id, order_date, cust_id
FROM order
WHERE order_date > TO_DATE('2018-02-15', 'YYYY-MM-DD');

We can use it on text values as well, where a comparison is performed based on the characters in the string:

SELECT id, first_name, address_state
FROM customer
WHERE first_name > 'Andy';

It means that the “greater than” is calculated based on where the text value would appear if it was sorted alphabetically.

For example, the above query mentions:
WHERE first_name > ‘Andy’

This would show records where the first name is:

• Anne (because “Ann” comes after “And”)
• Arthur (because “Art” comes after “And”)
• Brad (because it starts with B which is after A)

But it would not show:

• Andrew (because “Andr” comes before “Andy”)
• Alex

Greater Than or Equal To

If the greater than symbol is >, then we can check for greater than or equal to by using the symbol >=. It’s a combination of greater than and the equals symbol.

It will find any values that are greater than or equal to the specified value.

WHERE expression >= expression

We can use it in queries on numbers:

SELECT id, first_name, address_state
FROM customer
WHERE id >= 11;

We can also use it on dates:

SELECT order_id, order_date, cust_id
FROM order
WHERE order_date >= TO_DATE('2018-04-21', 'YYYY-MM-DD');

We can use it on text values as well, where a comparison is performed based on the characters in the string:

SELECT id, first_name, address_state
FROM customer
WHERE first_name >= 'John';

Less Than

The less than operator is the angled bracket <, which points left. It’s used to find values that are less than other values.

WHERE expression < expression

We can use it in queries on numbers:

SELECT id, first_name, address_state
FROM customer
WHERE id < 19;

We can also use it on dates:

SELECT order_id, order_date, cust_id
FROM order
WHERE order_date < TO_DATE('2017-12-31', 'YYYY-MM-DD');

We can use it on text values as well, where a comparison is performed based on the characters in the string:

SELECT id, first_name, address_state
FROM customer
WHERE first_name > 'Denise';

Less Than or Equal To

Just like greater than has a “greater than or equal to”, there is a “less than or equal to” operator.

It’s a combination of the less than symbol and an equals sign <=.

It will find any values that are less than or equal to the specified value.

WHERE expression <= expression

We can use it in queries on numbers:

SELECT id, first_name, address_state
FROM customer
WHERE id <= 8;

We can also use it on dates:

SELECT order_id, order_date, cust_id
FROM order
WHERE order_date <= TO_DATE('2018-01-10', 'YYYY-MM-DD');

We can use it on text values as well, where a comparison is performed based on the characters in the string:

SELECT id, first_name, address_state
FROM customer
WHERE first_name <= 'Miranda';

Copyright de Ben Brumm - DatabaseStar