Mastering the Not Equal Operator in SQL: Syntax, Usage, and Practical Examples

Introduction

SQL is the most sought-after language for data manipulation and database management. It comes with a wide range of built-in functions catering to a variety of data editing tasks. In this article, I will introduce you to one of the main functions used in SQL – the NOT EQUAL operator. This fundamental tool in SQL helps to filter data that doesn’t meet specific criteria. It allows you to exclude rows from your query results based on comparisons between values in your database tables. Let’s find out how the ‘not equal’ operator works and how exactly to use it.

If you’re just starting out to explore SQL, here’s a beginner’s guide to help you: SQL For Data Science: A Beginner Guide

Overview

• Understand the ‘not equal’ operator in SQL.
• Learn the syntax of the operator and where to use it.
• Practice using the ‘not equal’ operator in a sample dataset along with other SQL operators and functions.

Syntax of the Not Equal Operator

In SQL, the ‘not equal’ operator can be represented in two ways:

1. <> (preferred for adhering to ISO standards)
2. !=

Both operators are used interchangeably and function identically. The basic syntax for using the ‘not equal’ operator in an SQL query is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name <> value;

or

SELECT column_name(s)
FROM table_name
WHERE column_name != value;

Where to Use the Not Equal Operator

The ‘not equal’ operator is commonly used in the `WHERE` clause of a SQL query to filter out rows that don’t meet a specific condition. It’s useful in various scenarios, such as:

• Excluding certain records from query results.
• Filtering data based on multiple conditions.
• Combining with other SQL clauses and functions like `GROUP BY`, `ORDER BY`, and `HAVING`.

Sample Data

Let’s consider the following sample data for demonstration purposes:

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES
(1, 'John', 'Doe', 'HR', 50000.00),
(2, 'Jane', 'Smith', 'Finance', 60000.00),
(3, 'Emily', 'Jones', 'IT', 70000.00),
(4, 'Michael', 'Brown', 'Finance', 55000.00),
(5, 'Laura', 'Wilson', 'IT', 65000.00);

Using the Not Equal Operator with Different Operators and Functions

Now let’s learn how to apply the ‘not equal’ operator to our sample data.

Using NOT EQUAL with GROUP BY

To group employees by department and exclude those from the ‘IT’ department:

SELECT Department, COUNT(*) as NumEmployees
FROM Employees
WHERE Department <> 'IT'
GROUP BY Department;

Using NOT EQUAL with Text

To retrieve employees whose last name is not ‘Smith’:

SELECT FirstName, LastName, Department
FROM Employees
WHERE LastName != 'Smith';

Using NOT EQUAL with Negation Condition

To find employees who do not work in the ‘HR’ department and have a salary not equal to 60000.00:

SELECT FirstName, LastName, Department, Salary
FROM Employees
WHERE Department != 'HR' AND Salary <> 60000.00;

Using NOT EQUAL with Multiple Conditions

To get employees who are not in the ‘Finance’ department and have a salary greater than 55000.00:

SELECT FirstName, LastName, Department, Salary
FROM Employees
WHERE Department <> 'Finance' AND Salary > 55000.00;

Conclusion

I hope by now you have understood how to use the ‘not equal’ operator in SQL. It is a great tool for filtering out data that doesn’t meet specific criteria. Mastering this operator can really improve your ability to manage and analyze data in SQL databases. You can even combine it with other SQL functions and conditions, to create detailed queries and get precise insights from your data. So, go ahead, and try out this newly-learned operator on your databases!

Learn More: SQL: A Full Fledged Guide from Basics to Advanced Level

Frequently Asked Questions

Abhishek Kumar 24 Jun, 2024

Hello, I'm Abhishek, a Data Engineer Trainee at Analytics Vidhya. I'm passionate about data engineering and video games I have experience in Apache Hadoop, AWS, and SQL,and I keep on exploring their intricacies and optimizing data workflows :)