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:

1. SQL Equal Operator (=)
2. SQL Not Equal Operator (!=)
3. SQL Greater Than Equals to Operator (>=)
4. SQL Less Than Operator (<)
5. SQL Greater Than Operator (>)
6. 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:

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

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

Example of SQL Equal operator

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

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

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

Output:

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

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

Output:

The following query deletes the record of those employees whose Emp_City is '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:

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

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

Example of SQL NOT Equal operator

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

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

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

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

Output:

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:

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

Output:

The following query deletes the record of those cars whose Car_Name is not equal to 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:

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

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

Example of SQL Greater Than Operator

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

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

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

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

Output:

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:

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:

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

Output:

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:

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

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

Example of SQL Less Than operator

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

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

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

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

Output:

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:

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

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

Example of SQL Less Than Equals To Operator

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

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:

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

Output: