Back to cookbooks list Articles Cookbook

How to Check ‘Is Not Equal’ in SQL

  • <>

Problem:

You want to check if two values are not equal in SQL.

Example:

A social platform’s database has a table named employees with data in the columns first_name, last_name, and hire_date.

first_namelast_namehire_date
OliverCrawford2021-03-15
WilliamParker2022-01-22
RalphCrawford2009-10-01
LaneWalters2020-03-11
KeithPope2021-07-08
JeffreyRead2021-09-25

We want to select all employees except those with the last name of Crawford – i.e. we want all surnames not equal to “Crawford”.

Solution:

To filter out employees with the last name Crawford, you can use the != operator. This operator checks that the values on either side of it are not equal. Let’s see the solution for the example above:

SELECT * 
FROM employees 
WHERE last_name != 'Crawford';

Alternatively, you can use the <> operator, which also means ‘does not equal’ or ‘does not match’. Both operators work in the same way and both will return the same result in all SQL languages.

SELECT * 
FROM employees 
WHERE last_name <> 'Crawford';

With either query, this is what you’ll get:

first_namelast_namehire_date
WilliamParker2022-01-22
LaneWalters2020-03-11
KeithPope2021-07-08
JeffreyRead2021-09-25

Discussion:

Both != and work across popular SQL dialects: SQL Server, MySQL, PostgreSQL, Oracle SQL, and SQLite. Both will get you the same result. Both operators work with other data types, including integers, floats, dates, etc.

Let’s explore another example.

Example 2:

The social platform’s database has a table named error_report with data in the columns user_id, error_code, and report_date.

user_idreport_dateerror_code
VBJ16J2021-03-151
AD57232022-01-22404
G8FA5N2009-10-01301
997TT22020-03-111
17NBF62021-07-08402
HSIMAO2021-09-25405
BK58180001-01-011001
78936H0001-01-011001

We want to select all reports except those with the code 1.

Solution 2:

Let’s use the != operator to compare values in the column error_date and the error code value 1:

SELECT * 
FROM error_report
WHERE error_date != 1;

This is the result:

user_idreport_dateerror_code
AD57232022-01-22404
G8FA5N2009-10-01301
17NBF62021-07-08402
HSIMAO2021-09-25405
BK58180001-01-011001
78936H0001-01-011001

The last two records have obviously wrong dates. Let’s filter out these records.

Solution 3:

To eliminate records with faulty dates from the results, let’s add another comparison between the date in the column report_date and the value '0001-01-01':

SELECT * 
FROM error_report
WHERE error_date != 1 
AND report_date <> '0001-01-01';

And here’s our result:

user_idreport_dateerror_code
AD57232022-01-22404
G8FA5N2009-10-01301
17NBF62021-07-08402
HSIMAO2021-09-25405

Recommended courses:

Recommended articles:

See also: