SQL Operators: The Complete Guide

Do you want to know the different ways of comparing data in SQL, using operators?

SQL operators include EXISTS, IN, LIKE, BETWEEN, and many more. We’ll look at each of them in this guide.

By the end of the guide, you’ll have a solid understanding of these operators and will be able to use them in your own SQL code.

What Are SQL Operators?

SQL operators are symbols and keywords that are used to compare data.

They are commonly used in the WHERE clause of a query.

If you’ve learnt about the WHERE clause (which is used to filter your results to match a criteria), you would have learnt a little about operators.

The equals sign = is an operator, which means “is equal to”. When used in a where clause, it says “where some_expression is equal to another_expression”.

So, they help you compare data. They are often used in the WHERE clause but can be used in other places such as the HAVING clause and joins.

 

List of Operators in SQL

This table shows a list of operators available in SQL. This list uses standard ANSI SQL, so should be applicable to all databases that use that standard (Oracle, SQL Server, MySQL, etc). We’ll look at all of these operators in this guide in more detail.

Operator	Meaning
=	Is equal to
!=	Is not equal to
<>	Is not equal to
>	Is greater than
>=	Is greater than or equal to
<	Is less than
<=	Is less than or equal to
IN	Is in a list of values
NOT IN	Is not in a list of values
EXISTS	Is found in a result set
NOT EXISTS	Is not found in a result set
LIKE	Is a partial match
NOT LIKE	Is not a partial match
BETWEEN	Is between two values
NOT BETWEEN	Is not between two values
ANY	Matches at least one of a list of values
ALL	Matches all of a list of values

Let’s take a look at these operators.

 

Basic Operators

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';

 

Checking Lists of Values with IN and NOT IN

If you’re writing a query that has one value you want to check, then it can be a simple WHERE clause to write:

SELECT id, first_name, last_name
FROM customer
WHERE first_name = 'John';

What if you want to find records that match multiple conditions?

You can use the OR keyword to check if a value matches one or more values.

SELECT id, first_name, last_name
FROM customer
WHERE first_name = 'John'
OR first_name = 'Mary'
OR first_name = 'Susan';

This isn’t so bad when there is only a small list. But it can get messy when the list gets longer.

Surely there’s a better way!

Yes, there is!

It’s called the IN keyword.

 

The SQL IN Keyword

The IN keyword in SQL lets you check if an expression matches any of the specified values, in a single criteria. It avoids the need for many separate WHERE clauses and has a few other advantages.

You use the IN keyword, with some brackets, and specify your values inside those brackets.

WHERE expression IN (value_1, value_n….)

You can put a lot of values inside the brackets – up to 1,000 actually.

So, to rewrite the earlier example to use the IN clause, it would look like this:

SELECT id, first_name, last_name
FROM customer
WHERE first_name IN ('John', 'Mary', 'Susan');

This will show all customers that have a first_name of John, Mary, or Susan.

This will perform an “equal to” match. It will check if the first_name is equal to any of these values.

If you’re looking to perform other operators, such as greater than, have a read of the ANY and ALL section below.

So why would you use the IN keyword?

• It’s easier to type when you have more than one value to check.
• It’s easier to add and remove values from your query, rather than removing entire lines of a WHERE clause.
• It can be used with multiple values from subqueries.

A common way to use the IN clause is to use subqueries. I’ll have a guide on subqueries coming out soon that goes deeper into that topic, but a subquery is a query inside another query.

Using it with an IN keyword could look like this:

SELECT id, first_name, last_name
FROM customer
WHERE first_name IN (
  SELECT first_name
  FROM common_names
);

This means that all of the first_name values in the common_names table will be used as an input into the WHERE clause on the customer table.

Now, this may not be the most realistic example, but it’s just used to demonstrate the concept.

 

The SQL NOT IN Keyword

We just looked at the IN keyword, which checks if a value matches one of a range of values.

What if we want to find records that don’t match a range of values?

We can do this with the NOT IN keyword.

In SQL, many of the keywords can be reversed by adding the word NOT in front of them, as you’ll see in this article.

So, while the IN acts like an = on multiple values, NOT IN acts like a <> on multiple values.

WHERE expression NOT IN (value_1, value_n…)

Like the IN, you can put up to 1,000 values inside the NOT IN clause.

Let’s say you had a query like this:

SELECT id, first_name, last_name
FROM customer
WHERE first_name <> 'John'
AND first_name <> 'Mary'
AND first_name <> 'Susan';

This would find all of the customer records where the name is not John, and not Mary and not Susan.

This example uses AND because we want to find records that don’t match all of these values.

We can convert this to NOT IN by adding the values inside the brackets.

SELECT id, first_name, last_name
FROM customer
WHERE first_name NOT IN ('John', 'Mary', 'Susan');

This would show us the same results.

NOT IN can also be used with subqueries in the same way as IN:

SELECT id, first_name, last_name
FROM customer
WHERE first_name NOT IN (
  SELECT first_name
  FROM common_names
);

This means that all of the first_name values in the common_names table will be used as an input into the WHERE clause on the customer table, and all customer records that don’t match those values are returned.

 

More Information

Here are some more resources on learning about IN:

• Oracle documentation
• T-SQL (SQL Server) documentation
• NOT IN clause and NULL values

 

Checking Results with EXISTS and NOT EXISTS

There are two other keywords that can be used in a similar way: EXISTS and NOT EXISTS.

 

The SQL EXISTS Keyword

The SQL EXISTS keyword is used to check if at least one value is found in a subquery.

It doesn’t work with a literal list of values like the IN keyword does. So this clause won’t work: WHERE column EXISTS (value1, value2). It needs to be a subquery.

WHERE EXISTS (subquery);

The subquery is a SELECT query that aims to return at least one value.

If the subquery returns at least one value, EXISTS evaluates to TRUE and the record is shown.

If the subquery returns no values, then EXISTS evaluates to FALSE and the record is not shown.

Let’s see an example.

SELECT id, first_name, last_name
FROM customer
WHERE EXISTS (
  SELECT first_name
  FROM common_names
  WHERE customer.first_name = common_names.first_name
);

For each of the records in the customer table, this will find all of the first_name values from the common_names table that match the customer’s first name.

It performs the same kind of logic as the IN keyword.

But there are some differences – which I’ll explain shortly.

 

The SQL NOT EXISTS Keyword

The NOT EXISTS keyword is used to see if a value is not returned by a subquery.

The NOT EXISTS will check the results from a subquery, and return TRUE if no results are found in the subquery. It’s the opposite of EXISTS.

WHERE NOT EXISTS (subquery)

For example:

SELECT id, first_name, last_name
FROM customer
WHERE NOT EXISTS (
  SELECT first_name
  FROM common_names
  WHERE customer.first_name = common_names.first_name
);

This query will find all customer rows where the first_name is not in the common_names table.

 

What’s the Difference Between EXISTS, IN, and a Join?

So we’ve taken a look at both the IN and the EXISTS keywords, and their negative versions (using the NOT keyword).

What are the differences between IN and EXISTS?

• IN can be run with specific values or a subquery. EXISTS must use a subquery.
• IN checks all values for a match. EXISTS only checks if at least one value is returned.

The EXISTS keyword is intended as a way to avoid checking all of the data and avoid counting.

SELECT id, first_name, address_state
FROM customer
WHERE EXISTS (
  SELECT order_id
  FROM order
  WHERE order.customer_id = customer.id
);

This will find customers that have at least one order. It is likely to be more efficient than this query, as this query needs to check all of the records:

SELECT id, first_name, address_state
FROM customer
WHERE id IN (
  SELECT customer_id
  FROM order
);

IN is also better if you have a specific list to use, as you can just enter those values and not use an EXISTS with a subquery.

SELECT id, first_name, address_state
FROM customer
WHERE first_name IN ('John', 'Mary', 'Susan');

What about with a join? The example we saw earlier checks if first name values match between two tables, which sounds like a join.

SELECT id, first_name, last_name
FROM customer
INNER JOIN common_names ON customer.first_name = common_names.first_name;

This should return the same results as the IN query. Depending on your database, they should also evaluate to the same process and plan internally.

 

Why Do Queries SELECT Literal Values with EXISTS?

Have you ever seen a query like this?

SELECT id, first_name, address_state
FROM customer
WHERE EXISTS (
  SELECT 1
  FROM order
  WHERE order.customer_id = customer.id
  AND order_value > 100
);

Notice the SELECT clause in the subquery: it shows SELECT 1.

Why would this be there?

You might have seen this in online examples or in your project’s code.

The reason this is done is to improve the performance and to make the query easier to understand.

Because the subquery in the EXISTS clause only checks to see if at least one record is returned, it doesn’t actually care what value is returned.

Unlike the IN keyword, the value isn’t used. So, you can actually select any column from the table in the subquery.

Or, you can select a literal value like this example. This will select the literal value of 1 instead of a column from the database.

It’s one less thing the database needs to do.

 

More Information

Here are some more articles on EXISTS:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server
• Difference between EXISTS and IN in SQL?
• Consider using [NOT] EXISTS instead of [NOT] IN (subquery)

 

Performing Partial Matches with LIKE and NOT LIKE

So far we’ve checked for equals and not equals, and several other operators, on an exact match of a value.

What if we want to perform a partial match?

We can do that in SQL with the LIKE keyword.

The LIKE keyword in SQL allows us to perform a partial match on a value that is provided, where we can use wildcards in place of other values to perform a partial match search.

We can find all values that start with A, for example, no matter what the actual value is.

WHERE expression LIKE comparison_value

The query will check that the expression matches the logic in the comparison value.

So how can we use the LIKE keyword to look for partial matches?

We use wildcards.

Wildcards are special characters in SQL that represent other characters.

There are two: an asterisk * and an underscore _.

Character	Name	Meaning
*	Asterisk	Zero, one, or more of any character
_	Underscore	One of any character

So, an asterisk represents zero/one/many of any character, and an underscore represents one of any character.

 

How To Use Wildcards in SQL

Using wildcards in SQL means you add the characters inside a string you’re checking.

They can go at the start, end, in the middle: anywhere in the string. It just depends on what result you’re looking for.

An example of adding a wildcard to the end of the string would be ‘Ad*’. We are checking for a value of ‘Ad*’, the database will look for values that start with ‘Ad’ and any number of characters after it. It will find values such as ‘Adam’, ‘Adela’ and ‘Ado’ but not ‘Amy’ (because it doesn’t start with ‘Ad’).

Our query would look like this:

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE 'Ad*';

We could also look for a value of ‘*son’. This will find all values that end in ‘son’ with any number of characters coming before it. It would find values such as ‘Donaldson’, ‘Johnson’ and ‘Simpson’ but not ‘Song’ as it doesn’t end in ‘son’.

SELECT first_name, last_name
FROM customer
WHERE last_name LIKE '*son';

We can use a wildcard in the middle of a string. For example, a string of ‘A*d’ will find all values that start with A, have any number of characters in the middle, and end with ‘d’.

It would find values such as ‘And’, ‘Almond’ and ‘Armoured’ but not ‘Antidote’.

SELECT first_name, last_name
FROM customer
WHERE last_name LIKE 'A*d';

An underscore is used to find a single character. It can be used anywhere in the string, just like an asterisk.

For example, a string of ‘_on’ will find values such as ‘Son’ and ‘Don’ but not ‘Spoon’ (as it contains more than one character).

A query would look like this:

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE '_on';

Using wildcards can be a good way to find the data you need if it’s not an exact match.

 

Can I Use LIKE with IN?

Unfortunately we can’t. IN uses an exact match and is equivalent to =. LIKE is a partial match.

IN doesn’t support a list of partial match keywords.

To check multiple partial-match values, you would have to use separate WHERE clauses:

WHERE expression LIKE string1
OR expression LIKE string2

Or, you could use a regular expression, if the value can be expressed that way. I’ve written about regular expressions here if you want more information.

 

More Information

Here are some more articles on the LIKE keyword:

• T-SQL (SQL Server) documentation
• SQL ‘LIKE’ query using ‘%’ where the search criteria contains ‘%’
• Tuning SQL LIKE using indexes

 

Checking a Range of Values with BETWEEN and NOT BETWEEN

Sometimes we need to check if a value or expression is between two other values. For example, checking if a number is between two other numbers, or if a date is between two other dates.

One way to do this is to write two separate WHERE clauses:

SELECT *
FROM order
WHERE order_date <= TO_DATE('2018-12-31', 'YYYY-MM-DD')
AND order_date >= TO_DATE('2018-01-01', 'YYYY-MM-DD');

This will find orders that have an order date between 1st Jan and 31st Dec, 2018.

There is an easier way to do this though: using the BETWEEN keyword.

The BETWEEN keyword allows you easily filter data that is in a range between two values.

It looks like this:

WHERE expression BETWEEN value1 AND value2

This will check if your expression (e.g. a column) is between the two values mentioned. These values are inclusive: if the expression is equal to the value, it is included.

So, to rewrite our earlier query using BETWEEN, it would look like this:

SELECT *
FROM order
WHERE order_date BETWEEN TO_DATE('2018-01-01', 'YYYY-MM-DD')
AND TO_DATE('2018-12-31', 'YYYY-MM-DD');

I think it’s easier to read, and it’s clearer that you’re checking if a value is between two dates, which can often be overlooked if separate WHERE clauses are used.

It can also be used with numbers.

SELECT *
FROM order
WHERE order_amount BETWEEN 100 AND 500;

This will show all orders where the order_amount is greater than or equal to 100, and less than or equal to 500.

 

More Information

• T-SQL (SQL Server Documentation)
• Why is SQL’s BETWEEN inclusive rather than half-open?

 

Matching on Multiple Values with ANY and ALL

Another way to check data is to use the ANY and ALL commands.

These commands are not used that often, but they are good to know in case you come across any code that uses them.

 

SQL ALL Comparison

The SQL ALL comparison is used to check if an expression matches all values in a list.

It can be used with any of the basic operators: =, <>, >, <, >=, <=.

It looks like this:

WHERE expression = ALL (values)

Internally, this expression is translated into multiple WHERE clauses using the AND keyword.

So, a query like this:

SELECT id, first_name, last_name
FROM customer
WHERE first_name = ALL ('John', 'Mary', 'Susan', 'Mark')

Is the same as this query:

SELECT id, first_name, last_name
FROM customer
WHERE first_name = 'John'
AND first_name = 'Mary'
AND first_name = 'Susan'
AND first_name = 'Mark';

Now, looking at that query, there’s no way a first name can be equal to all of those values. So this query would return no results.

But, the ALL keyword is perhaps better used with another operator.

For example:

SELECT id, first_name, last_name
FROM customer
WHERE id > ALL (3, 8, 15);

This is the same as this query:

SELECT id, first_name, last_name
FROM customer
WHERE id > 3
AND id > 8
AND id > 15;

Using ALL, the criteria only needs to check against the minimum or maximum value. In this example, if an id is greater than 15, it’s greater than 3 and 8, so the checks for 3 and 8 are not needed. The query could be written as:

SELECT id, first_name, last_name
FROM customer
WHERE id > 15;

But that’s how you use the ALL operator. It’s not used very often that I’ve seen, but it’s good to know what it is.

 

SQL ANY Comparison

The SQL ANY comparison is similar to ALL. It is used to check if an expression matches all values in a list.

It can be used with any of the basic operators: =, <>, >, <, >=, <=.

It looks like this:

WHERE expression = ANY (values)

Internally, this expression is translated into multiple WHERE clauses using the OR keyword (The ALL operator used the AND keyword).

SELECT id, first_name, last_name
FROM customer
WHERE first_name = ANY ('John', 'Mary', 'Susan', 'Mark');

Is the same as this query:

SELECT id, first_name, last_name
FROM customer
WHERE first_name = 'John'
OR first_name = 'Mary'
OR first_name = 'Susan'
OR first_name = 'Mark';

It can also be written as an IN query:

SELECT id, first_name, last_name
FROM customer
WHERE first_name IN ('John', 'Mary', 'Susan', 'Mark');

If you use another operator, such as less than, the ANY keyword can look like this:

SELECT id, first_name, last_name
FROM customer
WHERE id < ANY (4, 9, 16);

This can also be written like this:

SELECT id, first_name, last_name
FROM customer
WHERE id < 4
OR id < 9
OR id < 16;

Because a value that is less than 4 is also less than 9 and 16, those can be removed. This query will return the same results:

SELECT id, first_name, last_name
FROM customer
WHERE id < 4;

 

Summary of ALL and ANY

The results of different combinations of ANY and ALL can be summarised as follows:

Criteria	Result
= ALL ()	The value must match all of the values in the list.
<> ALL ()	The value must not match any of the values in the list.
> ALL ()	The value must be greater than the largest value in the list.
< ALL ()	The value must be less than than the smallest value in the list.
>= ALL ()	The value must be greater than or equal to the largest value in the list.
<= ALL ()	The value must be less than or equal to than the smallest value in the list.
= ANY ()	The value must match one or more values in the list.
<> ANY ()	The value must not match one or more values in the list.
> ANY ()	The value must be greater than the smallest value in the list.
< ANY ()	The value must be less than than the largest value in the list.
>= ANY ()	The value must be greater than or equal to the smallest value in the list.
<= ANY ()	The value must be less than or equal to than the largest value in the list.

As mentioned earlier, you probably won’t find the need to use this syntax very often, but it’s good to know it exists.

 

More Information

Here are some more articles on ANY and ALL:

• Oracle Base: ANY ALL and SOME

 

Conclusion

There are many different operators in SQL. Some use symbols common in many other programming languages, such as = and >. Others use SQL keywords such as IN and EXISTS.

You’ll find yourself using these quite often, and knowing what they are and how they work will be a great help.

Do you have any questions on these operators? Are you wondering which one to use in a certain situation? Let me know in the comments below.