The equals operator (=) is probably the most commonly used operator in SQL. It compares the equality of two expressions. Even if you’ve only just begun your SQL journey, you’ll probably know this operator.
Source Table
Imagine our database contains the following table. This is the table we will use for the examples on this page.
SELECT * FROM Pets;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+ (8 rows affected)
Example
If we wanted to get all pets with a certain owner, we could do this:
SELECT *
FROM Pets
WHERE OwnerId = 3;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 4 | 3 | 3 | Wag | 2020-03-15 | +---------+-------------+-----------+-----------+------------+
Our query uses the equals operator (=) to compare the equality of the value of the OwnerId column and 3.
In this case, owner number 3 has 3 pets.
Strings
When comparing with a string value, use quotes around the string. For example, if we wanted to get information about all pets named Fluffy, we could do the following:
SELECT *
FROM Pets
WHERE PetName = 'Fluffy';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 6 | 3 | 4 | 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.
Multiple Conditions
If you have multiple conditions, you can use multiple equals operators.
Like this:
SELECT * FROM Pets
WHERE PetName = 'Fluffy' OR PetName = 'Tweet';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | +---------+-------------+-----------+-----------+------------+
We can see that it returned the rows where the PetName column was either Fluffy or Tweet.
Negating the Condition
You can use the NOT operator to negate the condition provided by the equals operator. Here’s an example:
SELECT *
FROM Pets
WHERE NOT PetName = 'Fluffy';Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
An alternative way of doing this is to use the not equal to operator, which is either <> or != (or both) depending on your DBMS.
NULL Values
You may have noticed that our original sample table contains a couple of NULL values in the DOB column.
A column containing NULL means that it has no value. This is different to 0 or false, or even an empty string.
You can’t use the equals operator to compare against NULL. Actually, this may depend on your DBMS and its configuration. But for now, let’s look at what happens if I try to compare the DOB column to NULL.
SELECT * FROM Pets
WHERE DOB = NULL;Result:
(0 rows affected)
The way to test for NULL is to use IS.
Therefore, we would need to rewrite the above statement as follows.
SELECT * FROM Pets
WHERE DOB IS NULL;Result:
+---------+-------------+-----------+-----------+-------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+-------| | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+-------+
NULL values can be a tricky thing for beginners to get their head around. Actually, they can be a tricky thing for experienced developers to get their head around! In SQL, NULL has a slightly different meaning to NULL in most other programming languages.
But the important thing to remember is that, in SQL, NULL means the lack of a value. There is no value. Therefore, we can’t say that it is equal or not equal to something else, because there is no value to start with.
If you’re interested, see SQL Server ANSI_NULLS Explained to see how you can change the way NULL values are treated in SQL Server.