SQL WHERE Condition for “Not equal to”

1. Overview

This tutorial explores the intricacies of using the WHERE condition for the “Not equal to” (<>) query. We start by understanding the WHERE clause’s basics and delve into the specifics of the <> condition, exploring different ways we can use it.

We’ll use the university database schema, which consists of the student, courses, and departments table, as examples to provide a rich context for our SQL queries.

The techniques we’ll see are fully supported by MySQL, SQL Server, and PostgreSQL.

2. Understanding the SQL WHERE Clauses

The WHERE clause is one of the fundamental components of SQL that facilitates the filtering of the rows returned by a SELECT, UPDATE, or DELETE statement. It acts as a gatekeeper, letting through only the data needed that meets specific conditions. Without the SQL WHERE clause, our queries would return all rows from a specified table, which is usually not what we need at a particular time.

Therefore, the power of the WHERE clause lies in its flexibility of filtering. We can filter data with the WHERE clause based on various conditions, from simple equality checks to complex logical expressions.

Consequently, the WHERE clause makes the database interactions more targeted and efficient.

2.1. Basic Syntax of WHERE

To begin with, let’s look at the basic syntax of the WHERE clause:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

In the query above, condition can either be any expression that evaluates to true, false, or unknown. The database returns all the rows where the conditions are evaluated to true.

For example, let’s retrieve all students who enrolled in 2020:

SELECT id, name, enrollment_date
FROM Student
WHERE YEAR(enrollment_date) = 2020;
+------+-----------------+-----------------+
| id   | name            | enrollment_date |
+------+-----------------+-----------------+
| 1001 | John Liu        | 2020-01-15      |
| 1011 | Vikas Jain      | 2020-01-15      |
...
| 1101 | Jia Grey        | 2020-01-15      |
+------+-----------------+-----------------+

This query returns the id, name, and enrollment_date of all students who enrolled in 2020 from the Student table.

3. The <> Condition

The <> condition allows the exclusion of specific values from the query results, which can be as important as including values. This condition is useful when we want to find all records that don’t match a desired criterion.

SQL provides two operators for this purpose: <> and !=. Importantly, MySQL, SQL Server and PostgreSQL all support both the <> and != operators, making them versatile choices for cross-database compatibility.

3.1. Standard “Not equal to” Operator (<>)

The most common way to express “Not equal to” in SQL is by using the <> operator. This operator is part of the SQL standard and is widely supported across different database management systems.

Let’s use this operator to find all courses that aren’t in the Computer Science department:

SELECT id, name, department_id
FROM Course
WHERE department_id <> 1;
+-------+------------------------------------------+---------------+
| id    | name                                     | department_id |
+-------+------------------------------------------+---------------+
| CE121 | Geotechnical Engineering-I               |             4 |
| CE141 | Mechanics of Fluids-I                    |             4 |
...
| ME421 | Nanotechnology-III                       |             3 |
+-------+------------------------------------------+---------------+

We can see that the query above didn’t return any course from the Computer Science departments.

3.2. Alternative “Not equal to” Operator (!=)

The != operator is functionally equivalent to the <> operator, performing the same comparison. The operator was borrowed from other programming languages so that developers familiar with the != operator can use the operator.

Here’s the same query using the != operator:

SELECT id, name, department_id
FROM Course
WHERE department_id != 1;
+-------+------------------------------------------+---------------+
| id    | name                                     | department_id |
+-------+------------------------------------------+---------------+
| CE111 | Introduction to Structural Engineering   |             4 |
| CE161 | Statistics for Civil Engineers           |             4 |
...
| ME431 | Advanced Operations Management           |             3 |
+-------+------------------------------------------+---------------+

Therefore, this query produces a similar result as the previous one. The choice between != and <> is based on personal or team preference and consideration for cross-database compatibility.

4. Using <> in Practice

Now that we understand the basics of the <> condition, let’s explore how to use the operator effectively in real-world scenarios. We’ll look at simple queries, combine <> with other conditions, and finally look at some important considerations when using all these operators.

4.1. Combining <> With Other Conditions

In real-life analysis, we often need to combine multiple conditions to precisely execute the data needed. The <> condition can be used alongside other conditions using AND, OR, and parentheses for grouping.

For example, let’s find all active courses outside the Computer Science department, i.e., WHERE id is not equal to 1 and having more than 5 credits:

SELECT id, name, credits, department_id
FROM Course
WHERE department_id <> 1
   AND is_active = 'Yes'
   AND credits > 5;
+-------+------------------------------------------+---------+---------------+
| id    | name                                     | credits | department_id |
+-------+------------------------------------------+---------+---------------+
| CE111 | Introduction to Structural Engineering   |       7 |             4 |
| CE151 | Modeling Tools for Civil Engineers       |       7 |             4 |
...
| ME221 | Nanotechnology-II                        |       7 |             3 |
+-------+------------------------------------------+---------+---------------+

The query above shows how we can use the <> operator alongside equality checks and the greater than comparisons.

4.2. Handling NULL Values

When working with <>, we should pay adequate attention to the NULL values. In SQL, NULL represents an unknown or missing value, and it behaves differently from other values in comparison.

Let’s look at an example here. If we want to find all the students with a known GPA that isn’t 4.0, we need to handle all the NULL values explicitly:

SELECT id, name, gpa
FROM Student
WHERE gpa IS NOT NULL
   AND gpa <> 4.0;
+------+-----------------+------+
| id   | name            | gpa  |
+------+-----------------+------+
| 1003 | Rita Ora        |  4.2 |
| 1101 | Jia Grey        | 3.98 |
...
| 2009 | Pollards Grey   | 4.65 |
+------+-----------------+------+

This query first filters out students with NULL GPAs and then applies the <> condition.

5. Conclusion

In this article, we explored the <> condition in SQL’s WHERE clause. We’ve seen both the <> and != operators to filter out unwanted data, combine <> with other conditions for more complex queries, and handle special cases like NULL values.

Mastering the SQL WHERE condition for <> helps craft more precise and efficient SQL queries, whether we’re working with student records, course catalogs, or any other type of data.