SQL Not Equal Operator

Updated on 12/09/2024108 Views

Table of Content

Introduction

Within the domain of Structured Query Language (SQL), operators are essential for working with data inside databases. The SQL NOT EQUAL operator is one such crucial operator that enables users to filter data according to the inequality of some condition. In this article, we will discuss SQL NOT EQUAL operator in detail by understanding its syntax, use cases, and examples.

Overview

In SQL, operators play a very crucial role in formulating the SQL queries to get, change, and manage the data stored in SQL databases. The SQL NOT EQUAL operator, which can be denoted as '<>' or '!=' in various database systems, enables users to filter out records that do not meet specific criteria. By using this operator with the help of the WHERE clause, SQL developers can efficiently filter and narrow down the search or query result sets based on inequality conditions.

SQL NOT EQUAL Operator

Suppose you have been assigned a task where you have employee data, and you have to exclude the details of certain employees where the department is HR. So, in that case, it is not a good idea to manually check all the employees for their department names, as there can be thousands of employees working for the company. In cases like this, SQL, which is not an equal operator, can come into play and can make things easier by excluding employee details from certain departments.

SQL NOT EQUAL operator is basically used to filter out records that do not meet the given condition. Unlike equality operators like “==” or "===", which filter out records on the basis of matching conditions, the NOT EQUAL operator allows the retrieval of records that deviate from a particular criterion.

For example, 1<>2 checks that 1 is not equal to 2 and will return in this case. Also, 1<>1 will return false because it fails the inequality check condition.

Difference between SQL Not Equal Operator <> and !=

Both the operators <> and != have the same function of checking the inequality between two expressions. Both operators return the same output for the particular expressions. The main difference between <> and != is that <> is more in line with the ISO coding standards, whereas != does not follow it.

Also, some of the DBMSs, like SQL server software, employ the <> for inequality operations, whereas some allow both of the Operators to be used.

Syntax

SQL NOT EQUAL operator is used with WHERE Clause to check for the inequality of the condition:

SELECT column1, column2, ...FROM table_nameWHERE column_name <> value;

Return Value

The SQL NOT EQUAL operator returns the value 1 if the condition is satisfied and 0 when it is not satisfied. To check the return value, we can use the following code:

Example:

select 1<>2;

Output:

+------+| 1<>2 |+------+| 1 |+------+

Example:

select 1<>1;

Output:

+------+| 1<>1 |+------+| 0 |+------+

To understand this operator better, let’s take an example.

Example

Let’s create a sample table named "Employees,” and let’s add some data to it. After that, we will be using this sample table to create examples.

-- Create Employees tableCREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50), Salary DECIMAL(10, 2));-- Insert sample data into Employees tableINSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)VALUES (1, 'John', 'Doe', 'HR', 5000.00), (2, 'Jane', 'Smith', 'IT', 6000.00), (3, 'Alice', 'Johnson', 'Marketing', 5500.00), (4, 'Bob', 'Williams', 'Finance', 7000.00), (5, 'Emily', 'Brown', 'HR', 4800.00), (6, 'Michael', 'Jones', 'IT', 6200.00), (7, 'Sarah', 'Davis', 'Marketing', 5800.00), (8, 'David', 'Taylor', 'Finance', 7200.00);-- Viewing sample dataSELECT * FROM Employees;

Now, let’s see the data inside the Employees table:

+------------+-----------+----------+------------+---------+| EmployeeID | FirstName | LastName | Department | Salary |+------------+-----------+----------+------------+---------+| 1 | John | Doe | HR | 5000.00 || 2 | Jane | Smith | IT | 6000.00 || 3 | Alice | Johnson | Marketing | 5500.00 || 4 | Bob | Williams | Finance | 7000.00 || 5 | Emily | Brown | HR | 4800.00 || 6 | Michael | Jones | IT | 6200.00 || 7 | Sarah | Davis | Marketing | 5800.00 || 8 | David | Taylor | Finance | 7200.00 |+------------+-----------+----------+------------+---------+

Example 1: Now, suppose we want to see the details Of the employees who do not belong to the HR department. in that case, We can utilise SQL NOT EQUAL operator to do the task:

select * from Employees where Department<>'HR';

Output:

+------------+-----------+----------+------------+---------+| EmployeeID | FirstName | LastName | Department | Salary |+------------+-----------+----------+------------+---------+| 2 | Jane | Smith | IT | 6000.00 || 3 | Alice | Johnson | Marketing | 5500.00 || 4 | Bob | Williams | Finance | 7000.00 || 6 | Michael | Jones | IT | 6200.00 || 7 | Sarah | Davis | Marketing | 5800.00 || 8 | David | Taylor | Finance | 7200.00 |+------------+-----------+----------+------------+---------+

Example 2: Suppose we want to filter out employees whose first name length is not equal to 5.

select * from Employees where LENGTH(FirstName)<>5;

Output:

employeeid | firstname | lastname | department | salary ------------+-----------+----------+------------+--------- 1 | John | Doe | HR | 5000.00 2 | Jane | Smith | IT | 6000.00 4 | Bob | Williams | Finance | 7000.00 6 | Michael | Jones | IT | 6200.00

NOT EQUAL with Text

The SQL NOT EQUAL operator is not limited to numerical values; it can also be applied to textual data. For instance, suppose we want to retrieve records where the department is not 'HR'. We can achieve this by executing the following query:

SELECT EmployeeID, DepartmentFROM EmployeesWHERE LastName <> 'Taylor';

Output:

employeeid | department ------------+------------ 1 | HR 2 | IT 3 | Marketing 4 | Finance 5 | HR 6 | IT 7 | Marketing

NOT EQUAL with GROUP BY Clause

The NOT EQUAL operator can be used along with the GROUP BY clause to aggregate data based on specific criteria while excluding certain values. For example, consider the following query to group salary department-wise:

SELECT Department, AVG(Salary) AS AverageSalaryFROM EmployeesWHERE Department <> 'IT'GROUP BY Department;

Output:

department | averagesalary ------------+----------------------- Marketing | 5650.0000000000000000 Finance | 7100.0000000000000000 HR | 4900.0000000000000000

NOT EQUAL with Multiple Conditions

The NOT EQUAL operator can be used with multiples, and this can be done by using the AND or OR operator. This is really helpful in cases where we want to filter records based on multiple conditions. For example, we want to retrieve records of employees whose department is not 'HR' and whose salary is not $5000. We can formulate the following query:

SELECT EmployeeID, DepartmentFROM EmployeesWHERE Department <> 'HR' AND Salary <> 5000;

Output:

Negating a Condition Using NOT EQUAL

In SQL, the NOT EQUAL operator can basically be used with the NOT operator to negate a condition. In that way, it will filter out the rows that meet a specific condition. Let’s take a look at an example of the same.

SELECT *FROM EmployeesWHERE NOT Department <> 'HR’;

Output:

This query basically negates out and gives the employees with the department as HR.

Wrapping Up

In conclusion, we can say that NOT EQUAL operator in SQL is an important operator that is used to filter out records that do not meet the condition. This is really helpful in cases where we want to exclude the data that does not meet the condition.

We have discussed different examples and use cases in which we can use the NOT EQUAL operator:

NOT EQUAL with Text
NOT EQUAL with GROUP BY Clause
NOT EQUAL with Multiple Conditions
Negating a Condition Using NOT EQUAL

The NOT EQUAL operator enables users to customize queries to meet their unique analytical requirements, whether they are working with textual or numerical data. This promotes thorough data analysis and decision-making processes.

Frequently Asked Questions

Let’s discuss some frequently asked questions:

Q. What is not equal to NULL in SQL?

A. Not equal with NULL in SQL evaluates to true if the value is not NULL.

Q. Does <> in SQL include NULL?

A. Yes, <> in SQL does not include NULL values.

Q. What is the difference between is not null and <> in SQL?

A. The difference between IS NOT NULL and <> in SQL is that IS NOT NULL explicitly checks for non-NULL values, whereas <> will check the equality between two values.

Q. Is it equal to 0 or NULL in SQL?

A. No, it is not equal to 0 or NULL in SQL.