Comparison operators are an important part of most programming languages.
Comparison operators are used to compare two expressions. The result is either true or false. It could also be unknown. This could also be represented by either 1, 0, or NULL, depending on the language. These are typically known as “Boolean expressions”.
When used with databases, comparison operators can be used inside your SQL queries to filter data to a certain criteria.
Common Comparison Operators
Most programming languages support the following comparison operators in one form or another:
| OPERATOR | MEANING |
|---|---|
= | Equal to |
> | Greater than |
< | Less than |
>= | Greater than or equal to |
<= | Less than or equal to |
<> | Not equal to |
SQL uses the same syntax as listed in the above table.
Some languages use a different syntax in order to avoid confusion with assignment operators. For example, Python and JavaScript use == instead of =. ColdFusion on the other hand, uses eq.
Different Data Types
Some languages have a special operator to specify that both values must be of the same data type.
For example, JavaScript also includes the === operator that specifies that both values are equal and that they must be of the same data type. But if the data type is irrelevant, use == instead.
Examples
Greater Than Operator
Here’s an example of using the Greater Than operator in a T-SQL query.
SELECT Name, Population
FROM country
WHERE Population > 100000000
ORDER BY Population DESC;Result:
+--------------------+--------------+ | Name | Population | |--------------------+--------------| | China | 1277558000 | | India | 1013662000 | | United States | 278357000 | | Indonesia | 212107000 | | Brazil | 170115000 | | Pakistan | 156483000 | | Russian Federation | 146934000 | | Bangladesh | 129155000 | | Japan | 126714000 | | Nigeria | 111506000 | +--------------------+--------------+
In this example I used the Greater Than operator (>) to select only countries with a population greater than 100000000.
Equals Operator
Here’s another example, where I use the equals operator (=) to return data that is exactly equal to a given value.
SELECT CountryCode, Name
FROM city
WHERE CountryCode = 'AFG'Result:
+---------------+----------------+ | CountryCode | Name | |---------------+----------------| | AFG | Kabul | | AFG | Qandahar | | AFG | Herat | | AFG | Mazar-e-Sharif | +---------------+----------------+
In this case I return only those cities whose country code is equal to AFG.
This demonstrates that comparison operators aren’t just limited to numeric data types.
Dates
You can also use comparison operators on dates, for example:
SELECT * FROM Artists
WHERE ActiveFrom > '1990-01-01';Result:
+------------+----------------+--------------+ | ArtistId | ArtistName | ActiveFrom | |------------+----------------+--------------| | 5 | Devin Townsend | 1993-01-01 | | 8 | Maroon 5 | 1994-01-01 | | 9 | The Script | 2001-01-01 | | 14 | Karnivool | 1997-01-01 | | 15 | Birds of Tokyo | 2004-01-01 | +------------+----------------+--------------+
As you can see, the only results returned are those greater than (after) the specified date.
Greater Than or Equal To Operator
I can use the Greater Than or Equal To operator to include the specified date in the results.
SELECT * FROM Artists
WHERE ActiveFrom >= '1990-01-01';Result:
+------------+----------------+--------------+ | ArtistId | ArtistName | ActiveFrom | |------------+----------------+--------------| | 5 | Devin Townsend | 1993-01-01 | | 8 | Maroon 5 | 1994-01-01 | | 9 | The Script | 2001-01-01 | | 14 | Karnivool | 1997-01-01 | | 15 | Birds of Tokyo | 2004-01-01 | | 16 | Bodyjar | 1990-01-01 | +------------+----------------+--------------+
In this case, one extra row is returned (the last row).
Less Than Operator
Here’s what happens if I change my query to use a Less Than operator.
SELECT * FROM Artists
WHERE ActiveFrom < '1990-01-01';Result:
+------------+------------------------+--------------+ | ArtistId | ArtistName | ActiveFrom | |------------+------------------------+--------------| | 1 | Iron Maiden | 1975-12-25 | | 2 | AC/DC | 1973-01-11 | | 3 | Allan Holdsworth | 1969-01-01 | | 4 | Buddy Rich | 1919-01-01 | | 6 | Jim Reeves | 1948-01-01 | | 7 | Tom Jones | 1963-01-01 | | 10 | Lit | 1988-06-26 | | 11 | Black Sabbath | 1968-01-01 | | 12 | Michael Learns to Rock | 1988-03-15 | | 13 | Carabao | 1981-01-01 | +------------+------------------------+--------------+
I get all rows that are less than the specified date.
Comparing NULL Values
NULL values can complicate things slightly when using comparison operators. In a nutshell, you may get a different result, depending on how your environment is configured.
For example, in SQL Server, if you use the Equals operator (=) or the Not Equal operator (<>) to test against NULL, your result will depend on your ANSI_NULLS setting (i.e. whether it’s ON or OFF).
To test against NULL values, SQL Server also provides the IS NULL predicate, which works consistently, regardless of your ANSI_NULLS setting. This also includes the IS NOT NULL variation for testing against non-NULL values.
Below is an example that demonstrates what I mean.
ANSI_NULLS ON
First we set ANSI_NULLS to ON, then run a few tests against NULL.
SET ANSI_NULLS ON;
SELECT NULL
WHERE NULL = NULL;
SELECT NULL
WHERE 1 = NULL;
SELECT NULL
WHERE NULL IS NULL;
SELECT 'Not NULL'
WHERE 1 IS NOT NULL;
SELECT 'Not NULL'
WHERE 1 <> NULL;Result:
(0 rows affected) (0 rows affected) +--------------------+ | (No column name) | |--------------------| | NULL | +--------------------+ (1 row affected) +--------------------+ | (No column name) | |--------------------| | Not NULL | +--------------------+ (1 row affected) (0 rows affected)
ANSI_NULLS OFF
Now we set ANSI_NULLS to OFF, then run the same tests.
SET ANSI_NULLS OFF;
SELECT NULL
WHERE NULL = NULL;
SELECT NULL
WHERE 1 = NULL;
SELECT NULL
WHERE NULL IS NULL;
SELECT 'Not NULL'
WHERE 1 IS NOT NULL;
SELECT 'Not NULL'
WHERE 1 <> NULL;Result:
+--------------------+ | (No column name) | |--------------------| | NULL | +--------------------+ (1 row affected) (0 rows affected) +--------------------+ | (No column name) | |--------------------| | NULL | +--------------------+ (1 row affected) +--------------------+ | (No column name) | |--------------------| | Not NULL | +--------------------+ (1 row affected) +--------------------+ | (No column name) | |--------------------| | Not NULL | +--------------------+ (1 row affected)