When working with SQL databases, you may come across situations where you need to exclude certain records from your query results. The SQL not equal operator, represented by <> or !=, allows you to do just that. In this tutorial, we will explore how to use the not equal operator in SQL queries.
Syntax
The syntax for the not equal operator in SQL is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE column_name <> value;
or
SELECT column1, column2, ...
FROM table_name
WHERE column_name != value;
Examples
Let’s assume we have a table named “employees” with the following data:
| id | first_name | last_name | age |
|---|---|---|---|
| 1 | John | Smith | 25 |
| 2 | Jane | Doe | 30 |
| 3 | Bob | Johnson | 40 |
| 4 | Sarah | Lee | 35 |
| 5 | Mike | Brown | 20 |
Example 1: Using the not equal operator with a single value
Suppose we want to retrieve all employees except those with an age of 30. We can use the not equal operator as follows:
SELECT * FROM employees
WHERE age != 30;
Output:
| id | first_name | last_name | age |
|---|---|---|---|
| 1 | John | Smith | 25 |
| 3 | Bob | Johnson | 40 |
| 4 | Sarah | Lee | 35 |
| 5 | Mike | Brown | 20 |
Example 2: Using the not equal operator with NULL values
NULL values require special attention when using the not equal operator. In SQL, a comparison with NULL will always result in a NULL value. Therefore, if you want to retrieve records where a column is not NULL, you need to use the IS NOT NULL operator instead of the not equal operator.
Suppose we want to retrieve all employees who have a last name. We can use the IS NOT NULL operator as follows:
SELECT * FROM employees
WHERE last_name IS NOT NULL;
Output:
| id | first_name | last_name | age |
|---|---|---|---|
| 1 | John | Smith | 25 |
| 2 | Jane | Doe | 30 |
| 3 | Bob | Johnson | 40 |
| 4 | Sarah | Lee | 35 |
| 5 | Mike | Brown | 20 |
Troubleshooting
If you are not getting the expected results when using the not equal operator, make sure to double-check your syntax and the data stored in your database. Also, keep in mind the special case of NULL values and use the IS NOT NULL operator when appropriate.
Conclusion
The not equal operator in SQL allows you to exclude specific records from your query results. By using the examples provided in this tutorial, you should be able to use the not equal operator in your own SQL queries. Remember to always double-check your syntax and data to ensure accurate results.