SQL Comparison Operators

The SQL Operators which compare the values of two columns in the database tables are called as comparison operators.

In SQL, comparison operators are always used in the WHERE clause with the SELECT, UPDATE, and DELETE statements.

The comparison operators in SQL are categorized into the following six operators category:

SQL Equal Operator (=)
SQL Not Equal Operator (!=)
SQL Greater Than Equals to Operator (>=)
SQL Less Than Operator (<)
SQL Greater Than Operator (>)
SQL Less Than Equals to Operator (<=)

Let's discuss each comparison operator one by one in detail with examples.

SQL Equal Operator (=)

This type of comparison operator selects only those data from the table which matches the specified value.

This operator is highly used by the database users in Structured Query Language.

This operator returns TRUE rows from the database table if the value of the column is same as the value specified in the query.

The following syntax accesses the data from the table by using the Equal operator:

SELECT * FROM Table_Name WHERE Column_Name = Value;

The syntax to update the data in the table by using the Equal operator is given below:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name = Value;

The syntax to delete the data from the table by using the Equal operator is given below:

DELETE FROM Table_Name WHERE Field_Name = Value;

Example of SQL Equal operator

The following CREATE query creates the Employee table with five fields:

CREATE TABLE Employee
(
Employee_ID INT,
Emp_Name VARCHAR (50),
Emp_City VARCHAR (20),
Emp_Salary INT NOT NULL,
Emp_Bonus INT NOT NULL
) ;

The following SELECT query shows the data of the Employee table:

SELECT * FROM Employee;

Employee_Id	Emp_Name	Emp_City	Emp_Salary	Emp_Bonus
101	Anuj	Ghaziabad	35000	2000
102	Tushar	Lucknow	29000	3000
103	Vivek	Kolkata	35000	2500
104	Shivam	Goa	22000	3000

The following query shows the record of those employees from the Employee table whose Emp_Salary is 35000:

SELECT * FROM Employee WHERE Emp_Salary = 35000;

Output:

Employee_Id	Emp_Name	Emp_City	Emp_Salary	Emp_Bonus
101	Anuj	Ghaziabad	35000	2000
103	Vivek	Kolkata	35000	2500

The following query updates the Emp_Salary of those employees whose Emp_Bonus is 3000:

UPDATE Employee SET Emp_Salary = 35000 WHERE Emp_Bonus = 3000;

To check the result of the above UPDATE query, write the following statement:

SELECT * FROM Employee;

Output:

Employee_Id	Emp_Name	Emp_City	Emp_Salary	Emp_Bonus
101	Anuj	Ghaziabad	35000	2000
102	Tushar	Lucknow	35000	3000
103	Vivek	Kolkata	35000	2500
104	Shivam	Goa	35000	3000

The following query deletes the record of those employees whose Emp_City is 'Goa':

DELETE FROM Employee WHERE Emp_City = 'Goa';

SQL NOT Equal Operator (!=)

This type of comparison operator selects only those data from the table which does not match with the specified value.

This operator returns TRUE rows from the database table if the value of the column is not same as the value specified in the query.

The syntax to access the data from the table by using the NOT Equal operator is given below:

SELECT * FROM Table_Name WHERE Column_Name != Value;

The syntax to update the data in the table by using the NOT Equal operator is given below:

UPDATE Table_Name SET Column_Name = Value WHERE Field_Name != Value;

The syntax to delete the data from the table by using the NOT Equal operator is given below:

DELETE FROM Table_Name WHERE Field_Name != Value;

Example of SQL NOT Equal operator

The following CREATE query creates the Cars table with four fields:

CREATE TABLE Cars
(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;

The following INSERT query inserts the record of cars into the Cars table:

INSERT INTO Cars (Car_Number, Car_Name, Car_Amount, Car_Price)
VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000);

The following SELECT query shows the data of the Cars table:

SELECT * FROM Cars;

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Creta	3	900000
9258	Audi	2	1100000
8233	Venue	6	900000
6214	Nexon	7	1000000

The following query shows the record of those cars from the Cars table whose Car_Price is not equal to 900000:

SELECT * FROM Cars WHERE Car_Price != 900000;

Output:

Car_Number	Car_Name	Car_Amount	Car_Price
9258	Audi	2	1100000
6214	Nexon	7	1000000

The following query updates the Car_Name of those cars whose Car_Number is not equal to 9258 or whose Car_Amount is not equal to 6:

<p>UPDATE Cars SET Car_Name = 'Mercedes' WHERE Car_Number != 9258 OR Car_Amount != 6;</p>

To check the result of the above UPDATE query, write the following statement:

SELECT * FROM Cars;

Output:

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Mercedes	3	900000
9258	Audi	2	1100000
8233	Venue	6	900000
6214	Mercedes	7	1000000

The following query deletes the record of those cars whose Car_Name is not equal to Audi.

DELETE FROM Cars WHERE Car_Name != 'Audi';

SQL Greater Than Operator (>)

This type of comparison operator selects, modifies, and deletes only those data from the table which are greater than the value specified in the query.

The following syntax accesses the data from the table by using the Greater Than operator:

SELECT * FROM Table_Name WHERE Column_Name > Value;

The syntax to update the data in the table by using the Greater Than operator is given below:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name > Value;

The syntax to delete the data from the table by using the Greater Than operator is given below:

DELETE FROM Table_Name WHERE Field_Name > Value;

Example of SQL Greater Than Operator

The following CREATE statement creates the Cars_Details table with four fields:

CREATE TABLE Cars_Details
(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;

The following INSERT query inserts the record of cars into the Cars_Details table:

INSERT INTO Cars_Details (Car_Number, Car_Name, Car_Amount, Car_Price)
VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000);

The following SELECT query shows the data of the Cars_Details table:

SELECT * FROM Cars_Details;

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Creta	3	900000
9258	Audi	2	1100000
8233	Venue	6	900000
6214	Nexon	7	1000000

The following query shows the record of those cars whose Car_Number is greater than 6000:

SELECT * FROM Cars_Details WHERE Car_Number > 6000;

Output:

Car_Number	Car_Name	Car_Amount	Car_Price
9258	Audi	2	1100000
8233	Venue	6	900000
6214	Nexon	7	100000

SQL Greater Than Equals to Operator (>=)

This type of comparison operator retrieves, modifies, and deletes only those data from the table which are greater than and equal to the given value.

The syntax to access the data from the table by using Greater Than Equals To operator is given below:

SELECT Column_Name1, Column_Name2, ….., Column_NameN FROM Table_Name WHERE Column_Name >= Value;

The syntax to update the data in the table by using Greater Than Equals To operator is given below:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name >= Value;

The syntax to delete the data from the table by using Greater Than Equals To operator is given below:

DELETE FROM Table_Name WHERE Column_Name >= Value;

Example of SQL Greater Than Equals To Operator

The following CREATE statement creates the Student_Details table with five fields:

CREATE TABLE Student_Details
(
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR (50),
Student_MathsINT NOT NULL,
Student_English INT NOT NULL,
Student_Total_Marks INT NOT NULL
) ;

The following INSERT query inserts the record of student into the Student_Details table:

INSERT INTO Student_Details (Student_ID, Student_Name, Student_Maths, Student_English, Student_Total_Marks) VALUES (201, Anuj, 30, 60, 90),
(202, Tushar, 25, 100, 125),
(203, Vivek, 30, 90, 120),
(204, Shivam, 40, 80, 120);

The following SELECT query shows the data of the Student_Details table:

SELECT * FROM Student_Details;

Student_Id	Student_Name	Student_Maths	Student_English	Student_Total_Marks
201	Anuj	30	60	90
202	Tushar	25	100	125
203	Vivek	30	90	120
204	Shivam	40	80	120

The following query shows the record of those students from the Student_Details table whose Total_Marks is greater than and equal to 120.

SELECT * FROM Student_Details WHERE Student_Total_Marks>= 120;

Output:

Student_Id	Student_Name	Student_Maths	Student_English	Student_Total_Marks
202	Tushar	25	100	125
203	Vivek	30	90	120
204	Shivam	40	80	120

SQL Less Than Operator (<)

This type of comparison operator in SQL selects only those data from the table which are less than the given value.

The following syntax accesses the data from the table by using the Less Than operator:

SELECT * FROM Table_Name WHERE Column_Name < Value;

The syntax to update the data in the table by using the Less Than operator is given below:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name < Value;

The syntax to delete the data from the table by using the Less Than operator is given below:

DELETE FROM Table_Name WHERE Field_Name < Value;

Example of SQL Less Than operator

The following CREATE statement creates the Cars_Details table with four fields:

CREATE TABLE Cars_Details
(
Car_Number INT PRIMARY KEY,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;

The following INSERT query inserts the record of cars into the Cars_Details table:

INSERT INTO Cars_Details (Car_Number, Car_Name, Car_Amount, Car_Price)
VALUES (2578, Creta, 3, 1500000),
(9258, Audi, 2, 3000000),
(8233, Venue, 6, 900000),
(6214, Nexon, 7, 1000000);

The following SELECT query shows the data of the Cars_Details table:

SELECT * FROM Cars_Details;

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Creta	3	900000
9258	Audi	2	1100000
8233	Venue	6	900000
6214	Nexon	7	1000000

The following query shows the record of those cars whose Car_Amount is less than 6:

SELECT * FROM Cars_Details WHERE Car_Amount < 6;

Output:

Car_Number	Car_Name	Car_Amount	Car_Price
2578	Creta	3	900000
9258	Audi	2	1100000

SQL Less Than Equals to Operator (<=)

This type of comparison operator selects only those data from the table which are less than and equal to the given value.

The syntax to access the data from the table by using the Less Than Equals To operator is given below:

SELECT Column_Name1, Column_Name2, ….., Column_NameN FROM Table_Name WHERE Column_Name <= Value;

The syntax to update the data in the table by using the Less Than Equals To operator is given below:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name <= Value;

The syntax to delete the data from the table by using the Less Than Equals To operator is given below:

DELETE FROM Table_Name WHERE Column_Name <= Value;

Example of SQL Less Than Equals To Operator

The following CREATE statement creates the Student_Details table with five fields:

CREATE TABLE Student_Details
(
Student_Id INT NOT NULL,
Student_Name VARCHAR (50),
Student_MathsINT NOT NULL,
Student_English INT NOT NULL,
Student_Total_Marks INT NOT NULL
) ;

The following INSERT query inserts the record of student into the Student_Details table:

The following SELECT query shows the data of the Student_Details table:

SELECT * FROM Student_Details;

Student_Id	Student_Name	Student_Maths	Student_English	Student_Total_Marks
201	Anuj	30	60	90
202	Tushar	25	100	125
203	Vivek	30	90	120
204	Shivam	40	80	120

The following query shows the record of those students from the Student_Details table whose Student_Id is less than and equal to 202.

SELECT Student_Id, Student_Maths, Student_English, Student_Total_Marks FROM Student_Details WHERE Student_Id <= 202;

Output:

Student_Id	Student_Maths	Student_English	Student_Total_Marks
201	30	60	90
202	25	100	125

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz