Tutorial Playlist
46 Lessons1. SQL Tutorial
2. The Essential Guide To Understanding SQL In DBMS
3. SQL Commands
4. SQL Data Types
5. SQL Aliases
6. SQL INSERT INTO With Examples
7. Master SQL Update
8. SQL Delete Statement: A Complete Overview with Examples
9. SQL WHERE Clause
10. SQL AND Operator
11. SQL NOT Operator: A Comprehensive Guide
12. SQL Like
13. SQL Between Operator: A Complete Overview with Examples
14. Difference Between SQL and MySQL: Get to Know Your DBMS
15. MySQL Workbench
16. A Comprehensive Guide to MySQL Workbench Installation and Configuration
17. Mastering SQL: Your Comprehensive Guide to Becoming an SQL Developer
18. SQL CREATE TABLE With Examples
19. How To Add Columns In SQL: A Step-By-Step Guide
20. Drop Column in SQL: Everything You Need to Know
21. Index in SQL
22. Constraints in SQL: A Complete Guide with Examples
23. Schema in SQL
24. Entity Relationship Diagram (ERD) - A Complete Overview
25. Foreign Key in SQL with Examples
26. An Ultimate Guide to Understand all About Composite Keys in SQL
27. Normalization in SQL
28. Better Data Management: The Efficiency of TRUNCATE in SQL
29. Difference Between DELETE and TRUNCATE in SQL
30. SQL ORDER BY
31. SQL Not Equal Operator
Now Reading
32. SQL Intersect Operator: A Comprehensive Guide
33. SQL Union: Explained with Examples
34. SQL Case Statement Explained with Examples
35. Unleashing the CONCAT Function In SQL: String Manipulation Made Easy
36. Understanding and Mastering COALESCE in SQL
37. NVL in SQL
38. Understanding SQL Date Formats and Functions
39. DateDiff in SQL: A Complete Guide in 2024
40. SQL Wildcards
41. SQL DISTINCT: A Comprehensive Guide
42. LIMIT in SQL: A Comprehensive Tutorial
43. SQL Aggregate Functions
44. GROUP BY in SQL
45. SQL HAVING
46. EXISTS in SQL
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.
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.
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.
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.
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; |
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.
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 |
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 |
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 |
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:
employeeid | department ------------+------------ 2 | IT 3 | Marketing 4 | Finance 6 | IT 7 | Marketing 8 | Finance |
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.
employeeid | firstname | lastname | department | salary ------------+-----------+----------+------------+--------- 1 | John | Doe | HR | 5000.00 5 | Emily | Brown | HR | 4800.00 |
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:
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.
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.
Pavan Vadapalli
Director of Engineering
Motivated to leverage technology to solve problems. Seasoned leader for startups and fast moving orgs. Working on solving problems of scale and l… Read More
Talk to our experts. We’re available 24/7.
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enrolling. upGrad does not make any representations regarding the recognition or equivalence of the credits or credentials awarded, unless otherwise expressly stated. Success depends on individual qualifications, experience, and efforts in seeking employment.
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enr...