12 Commonly Used SQL Operators

Posted on by Ian

In SQL, an operator is a special character or keyword specifying an action that is performed on one or more expressions.

SQL operators are an integral part of SQL, and they enable us to write queries that return relevant results.

In this article, I present 12 of the most commonly used SQL operators when writing SQL queries.

The Equals (=) Operator

The Equals operator (=) is probably the most commonly used operator in SQL. It compares the equality of two expressions. Even if you’re no more than vaguely familiar with SQL, you’ll probably know this operator.

Imagine we have a database for a pet hotel, and we want to get information about all pets named Fluffy.

In that case, we could do this:

SELECT PetId, PetName, DOB 
FROM Pets
WHERE PetName = 'Fluffy';

Result:

+---------+-----------+------------+
| PetId   | PetName   | DOB        |
|---------+-----------+------------|
| 1       | Fluffy    | 2020-11-20 |
| 6       | Fluffy    | 2020-09-17 |
+---------+-----------+------------+

Our query uses the Equals operator (=) to compare the equality of the value of the PetName column and the string Fluffy.

In our case, we can see that the pet hotel currently has two pets called Fluffy.

The Greater Than (>) Operator

The Greater Than operator (>) compares two expressions and returns TRUE if the left operand has a value higher than the right operand; otherwise, the result is FALSE.

SELECT PetName, DOB 
FROM Pets
WHERE DOB > '2020-01-01';

Result:

+-----------+------------+
| PetName   | DOB        |
|-----------+------------|
| Fluffy    | 2020-11-20 |
| Wag       | 2020-03-15 |
| Tweet     | 2020-11-28 |
| Fluffy    | 2020-09-17 |
+-----------+------------+

In this case, it’s comparing the value of the DOB column and the date 2020-01-01 to see if the DOB column is greater.

The Less Than (<) Operator

The Less Than operator (<) does the opposite. It compares two expressions and returns TRUE if the left operand has a value lower than the right operand; otherwise, the result is FALSE.

SELECT PetName, DOB 
FROM Pets
WHERE DOB < '2020-01-01';

Result:

+-----------+------------+
| PetName   | DOB        |
|-----------+------------|
| Fetch     | 2019-08-16 |
| Scratch   | 2018-10-01 |
+-----------+------------+

The Greater Than or Equal To (>=) Operator

The Greater Than or Equal To operator (>=) compares two expressions and returns TRUE if the left operand has a value greater than or equal to the right operand; otherwise, it returns FALSE.

SELECT PetName, DOB 
FROM Pets
WHERE DOB >= '2020-09-17';

Result:

+-----------+------------+
| PetName   | DOB        |
|-----------+------------|
| Fluffy    | 2020-11-20 |
| Tweet     | 2020-11-28 |
| Fluffy    | 2020-09-17 |
+-----------+------------+

Note that it returned 2020-09-17 and all dates greater than that. If we had used the Greater Than operator (>), only the first two rows would have been returned.

The Less Than or Equal To (<=) Operator

The Less Than or Equal To operator (<=) compares two expressions and returns TRUE if the left operand has a value lower than or equal to the right operand; otherwise, the result is FALSE.

SELECT PetName, DOB 
FROM Pets
WHERE DOB <= '2020-09-17';

Result:

+-----------+------------+
| PetName   | DOB        |
|-----------+------------|
| Fetch     | 2019-08-16 |
| Scratch   | 2018-10-01 |
| Wag       | 2020-03-15 |
| Fluffy    | 2020-09-17 |
+-----------+------------+

Note that it returned 2020-09-17 and all dates less than that. If we had used the Less Than operator (<), only the first three rows would have been returned.

The AND Operator

The AND operator combines two Boolean expressions and returns TRUE when both expressions are TRUE.

Here’s an example.

SELECT PetId, DOB 
FROM Pets
WHERE PetName = 'Fluffy'
AND DOB > '2020-01-01';

Result:

+---------+------------+
| PetId   | DOB        |
|---------+------------|
| 1       | 2020-11-20 |
| 6       | 2020-09-17 |
+---------+------------+

In this case, we only get details of the pets where their name is Fluffy and their date of birth is between the two dates.

The OR Operator

The OR operator combines two Boolean expressions and returns TRUE when either of the conditions is TRUE.

Here’s an example.

SELECT 
    PetId, 
    PetName,
    DOB 
FROM Pets
WHERE PetName = 'Fluffy' OR PetName = 'Bark';

Result:

+---------+-----------+------------+
| PetId   | PetName   | DOB        |
|---------+-----------+------------|
| 1       | Fluffy    | 2020-11-20 |
| 6       | Fluffy    | 2020-09-17 |
| 7       | Bark      | NULL       |
+---------+-----------+------------+

This is basically the opposite of the AND operator. If we’d used AND, no rows would have been returned, because we can’t have two different values in one column at the same time.

The BETWEEN Operator

The BETWEEN operator allows us to specify a range to test. For example, we can use it to return pets who were born between two dates.

SELECT
    PetName,
    DOB
FROM Pets
WHERE DOB BETWEEN '2018-01-01' AND '2020-01-01';

Result:

+-----------+------------+
| PetName   | DOB        |
|-----------+------------|
| Fetch     | 2019-08-16 |
| Scratch   | 2018-10-01 |
+-----------+------------+

The NOT Operator

The NOT operator negates a Boolean input (it reverses the value of any Boolean expression). Therefore returns TRUE when the expression is FALSE.

Here’s what happens when we add the NOT operator to our previous example.

SELECT
    PetName,
    DOB
FROM Pets
WHERE DOB NOT BETWEEN '2018-01-01' AND '2020-01-01';

Result:

+-----------+------------+
| PetName   | DOB        |
|-----------+------------|
| Fluffy    | 2020-11-20 |
| Wag       | 2020-03-15 |
| Tweet     | 2020-11-28 |
| Fluffy    | 2020-09-17 |
+-----------+------------+

This time we got all the other pets – the pets that weren’t born between those dates.

In MariaDB, NOT is limited to negating the IN, BETWEEN, and EXISTS clauses. Most other DBMSs allow NOT to negate any expression.

The IN Operator

The IN operator determines whether a specified value matches any value in a subquery or a list.

Here’s an example.

SELECT 
    PetId, 
    PetName, 
    DOB 
FROM Pets
WHERE PetName IN ('Fluffy', 'Bark', 'Wag');

Result:

+---------+-----------+------------+
| PetId   | PetName   | DOB        |
|---------+-----------+------------|
| 1       | Fluffy    | 2020-11-20 |
| 4       | Wag       | 2020-03-15 |
| 6       | Fluffy    | 2020-09-17 |
| 7       | Bark      | NULL       |
+---------+-----------+------------+

We could achieve the same result by using two OR operators:

SELECT 
    PetId, 
    PetName, 
    DOB 
FROM Pets
WHERE PetName = 'Fluffy' 
    OR PetName = 'Bark' 
    OR PetName = 'Wag';

However, the IN operator is a more concise way of doing it. The IN operator is especially beneficial when you have a long list of values for which to compare against.

The IN operator almost always executes quicker than multiple OR operators, especially on larger data sets.

Another benefit of the IN operator, is that it can contain another SELECT list. This is known as a subquery. A subquery is a query that is nested inside another query (or even another subquery).

Here’s an example.

SELECT 
    PetTypeId,
    PetType
FROM PetTypes
WHERE PetTypeId IN ( SELECT PetTypeId FROM Pets );

Result:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 1           | Bird      |
| 2           | Cat       |
| 3           | Dog       |
+-------------+-----------+

This example shows us how many pet types are in our pet hotel.

We could use the NOT operator to flip this around and see how many pet types are not in our pet hotel.

SELECT 
    PetTypeId,
    PetType
FROM PetTypes
WHERE PetTypeId NOT IN ( SELECT PetTypeId FROM Pets );

Result:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 4           | Rabbit    |
+-------------+-----------+

So our database contains a pet type of Rabbit, but we don’t currently have any rabbits as pets.

The + and || String Concatenation Operators

The + and || string concatenation operators allows you concatenate strings. String concatenation is the operation of joining character strings end-to-end.

The reason I’ve listed two operators that do the same thing is because, the + operator is supported in SQL Server, and the || operator is supported in DB2, Oracle, PostgreSQL, SQLite.

Here’s an example that works in SQL Server.

SELECT 
    FirstName,
    LastName,
    FirstName + LastName
FROM Owners;

Result:

+-------------+------------+--------------------+
| FirstName   | LastName   | (No column name)   |
|-------------+------------+--------------------|
| Homer       | Connery    | HomerConnery       |
| Bart        | Pitt       | BartPitt           |
| Nancy       | Simpson    | NancySimpson       |
| Boris       | Trump      | BorisTrump         |
+-------------+------------+--------------------+

In this case, owners have their first name and last name stored in different columns. In our SELECT list, we first retrieve those individually, but we also retrieve them again, using a concatenation operator to join them together.

There are a couple of issues with our results though.

  • There’s no space between the first and last names.
  • There’s no column name.

Fortunately this is easy to fix.

We can use the same concatenation operation to include space between the first name and last name.

And we can use a column alias to the concatenated result in order to provide a column name in the result set.

SELECT 
    FirstName,
    LastName,
    FirstName + ' ' + LastName AS FullName
FROM Owners;

Result:

+-------------+------------+---------------+
| FirstName   | LastName   | FullName      |
|-------------+------------+---------------|
| Homer       | Connery    | Homer Connery |
| Bart        | Pitt       | Bart Pitt     |
| Nancy       | Simpson    | Nancy Simpson |
| Boris       | Trump      | Boris Trump   |
+-------------+------------+---------------+

If you’re using DB2, Oracle, PostgreSQL, SQLite, swap the + for || and it should work fine.

If you’re using MySQL or MariaDB, you’ll need to use the CONCAT() function to concatenate strings.

The LIKE Operator

The LIKE operator allows you to do pattern matching. It determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters.

SELECT 
    FirstName,
    LastName,
    Email
FROM Owners
WHERE Email LIKE '%.com';

Result:

+-------------+------------+-------------------+
| FirstName   | LastName   | Email             |
|-------------+------------+-------------------|
| Homer       | Connery    | [email protected] |
| Bart        | Pitt       | [email protected]  |
+-------------+------------+-------------------+

In this example, I used the LIKE operator in conjunction with the % wildcard operator to return owners whose email addresses end in .com.

Bonus Operator! The % Wildcard Operator

The % wildcard operator matches any string of zero or more characters. It can be used as either a prefix or a suffix, and it can also be used in the middle of a string.

It tends to be more commonly used as a suffix, and it has limited use in the middle of patterns, although there are some valid use-cases for using it in the middle of a pattern, such as the following:

SELECT 
    FirstName,
    LastName,
    Email
FROM Owners
WHERE Email LIKE 'bart@%.com';

Result:

+-------------+------------+------------------+
| FirstName   | LastName   | Email            |
|-------------+------------+------------------|
| Bart        | Pitt       | [email protected] |
+-------------+------------+------------------+

In this case, we didn’t know bart’s email address, but we knew it started with bart@ and ended in .com. Therefore, we were able to use LIKE in conjunction with the % wildcard operator to fill in the rest.

Bear in mind that this technique can return a lot of irrelevant rows, depending on the data, and how you construct your SQL statement.

Also be aware that queries that contain the LIKE clause can run a lot slower than other queries, and you should probably avoid using the LIKE clause unless you really need it. Using the % operator as a prefix can be particularly slow.

That is not to say you shouldn’t use it at all. The LIKE operator is an integral part of SQL, and you’ll encounter many scenarios where it will be the only option (or at least, the best option).

More SQL Operators

See my full list of SQL Operators for a whole bunch of other operators that aren’t included here.