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
<>
!=
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 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 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 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 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 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 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 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
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
Further Reading