SQL Not Equal Tutorial [Practical Examples]

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

Advertisement

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

Advertisement

 

SQL Not Equal Operator Examples

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

Patient Table

patient_idnameagegenderaddressdiseasedoctor_id
1reema23femalealthan,Suratfever21
2kusum50femalevadodaraheart failure22
3carlin43malevapiinfection23
4rahul26malenavsaricancer21
6hansha55femalevapidiabetes22

Doctor Table

doctor_idnameagegenderaddress
21asif55malebaruch
22dhawal40maleSurat
23krishna39femaleSurat
24lissa35femaleNavsari
25leeba34femalebaruch
26vini33femaleSurat
27Dhiren32maleNavsari

Bill Table

bill_nopatient_iddoctor_idroom_chargeno_of_days
500513405004
500626004808
500838003403
500947808906
501034001
501112003001
501226001102
501333302101
501412303402

Laboratory Table

lan_nopatient_iddoctor_iddateamount
1012102-02-20004000
2022109-09-2001300
3032203-03-2001600
4012302-06-2002800
5042105-07-2003900
6022510-04-2004550
7042203-04-2005900

 

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:

Advertisement

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

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

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 either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment