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.

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:

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.

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:

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: