SQL Not Equal Tutorial [Practical Examples]


Written by - Falguni Thakker
Reviewed by - Deepak Prasad

Overview of SQL Not Equal

SQL Not Equal is a comparison operator used to compare two not null operand values or an expression, if the left operand value is not equal to the right operand value, then the result will be TRUE otherwise the result is FALSE

If either or both operands are NULL, SQL Not Equal operator will return NULL

In SQL, we have various not equal operators

  1. <>
  2. !=

Which one we use depends on the DBMS we are using, which one you’re the most comfortable using, and perhaps also whether your organization has any coding conventions that dictate which one should be used

 

Difference between SQL Not Equal Operator <> and !=

We can use both operators <> and != to do an inequality test between two expressions. Both operators give the same output. The only difference is that ‘<>’ is in line with the ISO standard while ‘!=’ does not follow ISO standard

 

SQL Not Equal Operator Syntax

Expression | column_name <> Expression| Constant | Variable 
Expression | column_name  !=  Expression| Constant | Variable 

Here,

Expression | column_name <> Expression| Constant | Variable | column_name:

  • Left side Operand of SQL Not Equal is any valid expression or column name of the table
  • Right side Operand of SQL Not Equal is any valid expression, constant or any variable value to be compared with the left operand
  • Both left and right-side expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence

 

Return Type

The return type of SQL Not Equal operator is Boolean

 

SQL Not Equal Operator Examples

Consider Hospital database management system with four table patient, doctor, bill, and laboratory for practical examples
Patient Table

patient_id name age gender address disease doctor_id
1 reema 23 female althan,Surat fever 21
2 kusum 50 female vadodara heart failure 22
3 carlin 43 male vapi infection 23
4 rahul 26 male navsari cancer 21
6 hansha 55 female vapi diabetes 22

Doctor Table

doctor_id name age gender address
21 asif 55 male baruch
22 dhawal 40 male Surat
23 krishna 39 female Surat
24 lissa 35 female Navsari
25 leeba 34 female baruch
26 vini 33 female Surat
27 Dhiren 32 male Navsari

Bill Table

bill_no patient_id doctor_id room_charge no_of_days
5005 1 340 500 4
5006 2 600 480 8
5008 3 800 340 3
5009 4 780 890 6
5010 3 400 1
5011 1 200 300 1
5012 2 600 110 2
5013 3 330 210 1
5014 1 230 340 2

Laboratory Table

lan_no patient_id doctor_id date amount
10 1 21 02-02-2000 4000
20 2 21 09-09-2001 300
30 3 22 03-03-2001 600
40 1 23 02-06-2002 800
50 4 21 05-07-2003 900
60 2 25 10-04-2004 550
70 4 22 03-04-2005 900

 

SQL Not Equal with Single Numeric Expression

Example 1: Write SQL Query to display patients data except doctor id 21

SELECT patient_id, name, age, gender, address, disease, doctor_id FROM  patient 
WHERE (doctor_id <> 21)

In this query, SQL Not equal operator is used to compare column doctor_id with the constant value of 21

OUTPUT:

SQL Not Equal Tutorial [Practical Examples]

 

SQL Not Equal with Single String Expression

Example 2: Write SQL Query to display all-male patient lab report data

SELECT patient.patient_id, patient.name, patient.age, patient.gender, patient.address, patient.disease, patient.doctor_id FROM patient 
LEFT OUTER JOIN laboratory ON patient.patient_id = laboratory.patient_id
WHERE (patient.gender <> 'female')
  • In this query, SQL Not Equal Operator (<>) is used to compare gender column value with string value ‘Female’ to retrieve male patient data
  • SQL left join is used to join two tables patient and laboratory to combine the result of both the tables

OUTPUT:

SQL Not Equal Tutorial [Practical Examples]

 

SQL Not Equal with date Expression

Example 3: Write SQL Query to retrieve patient data who do not have any lab report in the year 2000

SELECT patient.patient_id, patient.name, patient.age, patient.gender, patient.address, patient.disease, patient.doctor_id, laboratory.lab_no, laboratory.date
FROM patient 
LEFT OUTER JOIN laboratory ON patient.patient_id = laboratory.patient_id
WHERE (YEAR(laboratory.date) <> 2000)
  • In the above query, SQL Not Equal operator is used to compare year with the date column of the laboratory table
  • SQL Year function is used to extract the year from the date value and SQL left join is used to combine two table columns in the result set

OUTPUT:

SQL Not Equal Tutorial [Practical Examples]

 

SQL Not Equal with multiple conditions

Example 4: Write SQL query to display all Female patient data who have not did any lab report in the year 2001

SELECT patient.patient_id, patient.name, patient.age, patient.gender, patient.address, patient.disease, patient.doctor_id, laboratory.lab_no, laboratory.date
FROM patient 
LEFT OUTER JOIN laboratory ON patient.patient_id = laboratory.patient_id
WHERE (YEAR(laboratory.date) != 2001) AND (patient.gender != 'male')
  • In this query, SQL Not equal used in two conditions one with the date to compare the year not equal to 2001 and another with the gender not equal to male
  • Logical operator AND is used in between two conditions to get the data for which both the conditions are true

OUTPUT:

SQL Not Equal Tutorial [Practical Examples]

 

SQL Not Equal with Group By clause

We can use the SQL Not Equal operator in combination with the SQL Group By clause to make a group of records and to fetch the records from each group based on condition

Example 5: Write SQL query to count total patients for each disease except infection

SELECT patient.disease, COUNT(patient.patient_id) AS total
FROM   patient LEFT OUTER JOIN bill ON patient.patient_id = bill.patient_id
GROUP BY patient.disease
HAVING (patient.disease <> 'infection')
  • In this query, SQL Not Equal operator is used with the Group by clause to make a group of records based on disease
  • SQL Count function is used to calculate total patient records for each group except infection which is specified with having clause condition

OUTPUT:

SQL Not Equal Tutorial [Practical Examples]

 

SQL Not Equal with Group by and with Multiple Conditions

Example 6: Write SQL Query to display disease and lab no wise total patient except patient of Infection disease and not having lab no as 10

SELECT patient.disease, laboratory.lab_no, COUNT(patient.patient_id) AS ' total patient'
FROM patient LEFT OUTER JOIN
laboratory ON patient.patient_id = laboratory.patient_id
GROUP BY laboratory.patient_id, laboratory.lab_no, patient.disease
HAVING (patient.disease != 'infection') AND (laboratory.lab_no <> 10)
  • In the above query, SQL Not Equal operator is used with two conditions in the having clause one with the disease column value which is compared for inequality with the constant value infection and another condition with the constant value of 10
  • SQL Group by clause is used to make a group pf patient in the laboratory table and the by lab no

OUTPUT:

SQL Not Equal Tutorial [Practical Examples]

 

SQL Not Equal with Delete statement

SQL Not Equal operator is also used with Delete query to delete the record from table based on the condition with where clause

Example 7: Write SQL Query to remove patient billing record except for patient of doctor id 22 and 24

DELETE FROM bill
WHERE (doctor_id <> 22) AND (doctor_id <> 24)
  • In this query, SQL Not Equal operator is used with where condition to compare and delete the record from table
  • When we execute this query, it shows a message of four rows deleted successfully, as in the bill table four records satisfied with these two conditions

OUTPUT:

To see the result of the above query, we need to use a Select statement

SQL Not Equal Tutorial [Practical Examples]

 

SQL Not Equal with an Update statement

We can use SQL Not Equal operator with Update query to conditionally update table records by comparing inequality

Example 8: Write SQL Query to update patient lab report data by adding 100rs in lab report amount except patient whose report amount is 900rs

UPDATE laboratory
SET amount = amount + 100
WHERE (amount <> 900)
  • In the above query, SQL Not Equal operator is used in update query to update patient lab report amount by comparing the value of amount column with 900
  • When we execute the above query its shows a message of four rows updated successfully, as four records in the laboratory table satisfied this condition

OUTPUT:

To see the modified record of the above query, we need to use a Select statement

SQL Not Equal Tutorial [Practical Examples]

 

Summary

In this article, the Overview of Not Equal comparison operator is explained, the difference between Not Equal (<>) and (!=) has covered, the Syntax and argument of syntax has been discussed, also the practical examples of SQL Not Equal with the single condition on numeric value and string value, example with multiple conditions is also covered,  with Group by and having clause is also covered and at the end of this article we have covered examples with Delete and Update statement

 

References

SQL Left Join
Overview of SQL

 

Further Reading

SQL NOT EQUAL TO

 

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn.

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment

X