In this article, we will show you SQL Not Equal Operator through detailed examples.
1. Introduction
SQL is a standard language for storing, manipulating, and retrieving data in a database. SQL stands for Structured Query Language. SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987. Although SQL is an ANSI/ISO standard, there are different versions of the SQL language. Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard.
In this article, we will look at what a SQL, not equal operator is and how to use it.
2. Setup
For our examples, we will use SQLite. SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. I am using Mac and SQLite comes to build in. Type SQLite on the terminal to check if it is installed on your machine or not:
~$ sqlite3
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
Let us first create a table where we will store the employee data. Run the below command in the SQLite prompt:
CREATE TABLE employee (
employee_id INTEGER PRIIMARY KEY,
first_name TEXT NOT NULL,
surname TEXT NOT NULL,
email TEXT NOT NULL,
department TEXT
);
To check if the table got created run .tables command. Now let us insert some data into the employee table:
INSERT INTO employee(employee_id, first_name, surname, email, department)
VALUES (1, 'Mike', 'Hertz', 'mike.hertz@jcg.com', 'Operations');
INSERT INTO employee(employee_id, first_name, surname, email, department)
VALUES (2, 'Ahmed', 'Basheer', 'ahmed.basheer@jcg.com', 'HR');
INSERT INTO employee(employee_id, first_name, surname, email, department)
VALUES (3, 'Andree', 'Coper', 'andree.coper@jcg.com', 'Finance');
To check if the records are added successfully run the SELECT command:
sqlite> select * from employee;
1|Mike|Hertz|mike.hertz@jcg.com|Operations
2|Ahmed|Basheer|ahmed.basheer@jcg.com|HR
3|Andree|Coper|andree.coper@jcg.com|Finance
sqlite>
3. SQL Not Equal operator
Let’s say we want to see the data of all the employees who do not work in the Operations department. To do this we will make use of the SQL not-equal (!=) operator:
sqlite> select * from employee where department != 'Operations';
2|Ahmed|Basheer|ahmed.basheer@jcg.com|HR
3|Andree|Coper|andree.coper@jcg.com|Finance
sqlite>
We can see that the query didn’t return the data of Mike as he works in the Operations department. The not-equal operator can also be applied to the column of type number. Let’s say we want to return the records of all the employees except the one with employee id 2:
sqlite> select * from employee where employee_id != 2;
1|Mike|Hertz|mike.hertz@jcg.com|Operations
3|Andree|Coper|andree.coper@jcg.com|Finance
sqlite>
The operator can also be joined with and/or operators to apply multiple filters, e.g:
sqlite> select * from employee where department != 'Operations' and department != 'HR';
3|Andree|Coper|andree.coper@jcg.com|Finance
One of the most useful places of this operator is when we want to filter the empty/null records. Let us say that we have some column which is optional, so we will have data for some rows but for some rows, it will be empty. We can use the <column_name> != NULL statement to filter the rows which don’t have values for that column.
Please note that in some RDBMS you can also use <> as a not-equal operator as well. Both != and <> operators are not equal operators and will return the same result but the != operator is not an ISO standard.
4. Summary
In this article, we looked at the SQL not equal operator. We looked at the different ways of using it and its most common usages. We saw that it can be used on numbers and on strings. We also looked at how easy it can be combined with other operators.
