SQL - Comparison Operators



The SQL comparison operators are used to compare two or more expressions and return a Boolean value (True or False) based on the comparison result. Here are some commonly used SQL comparison operators:

Operator Description Example
= Checks if the values of two operands are equal or not, if yes then condition becomes true. (a = b) is not true.
!= Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a != b) is true.
<> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. (a <> b) is true.
> Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. (a > b) is not true.
< Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. (a < b) is true.
>= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. (a >= b) is not true.
<= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. (a <= b) is true.
!< Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true. (a !< b) is false.
!> Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true. (a !> b) is true.

In this chapter we will learn about each operator one-by-one with examples showing its usage. Before proceeding further, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

Once the table is created, we can insert some values into the table using the following queries −

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'khilan', 25, 'Delhi', 1500.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'Kaushik', 23, 'Kota', 2000.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardhik', 27, 'Bhopal', 8500.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'komal', 22, 'MP', 4500.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', 24, 'Indore', 10000.00 );

Let us verify whether the table is created or not using the following query −

SELECT * FROM CUSTOMERS;

As we can see in the below output, the table has been created in the database.

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardhik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

Now, let us perform all the SQL comparison operations using the above table.

SQL Equal to (=) Operator

The equal to operator in SQL is used to check if two values are equal. It is used to filter rows with values that matches a specific condition.

Example

In the following query, we are trying to perform the Equal to (=) operation to retrieve all the customers with age equal to 25.

SELECT * FROM customers WHERE age = 25;

Output

When we execute the above query, the output is obtained as follows −

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00

SQL Not equal to (!= or <>) Operator

The not equal to operator in SQL checks if two expressions are not equal. It is used to filter rows with values that do not match a specific condition.

Example

In the following example, we are trying to retrieve all the customers whose age is not equal to 25 and also salary is not equal to 2000.

SELECT * FROM customers WHERE age != 25 and SALARY <> 2000;

Output

When we execute the above query, the output is obtained as follows −

ID NAME AGE ADDRESS SALARY
5 Hardhik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

SQL Greater than (>) Operator

The greater than operator in SQL is used to return rows having values greater than the specified value. It checks if the left expression is greater than the right expression. It is used to filter rows with values above a certain threshold.

Example

The following query retrieves all rows from the "customers" table where the "age" column is greater than 25.

SELECT * FROM customers WHERE age > 25;

Output

On executing the above query, the output is displayed as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
5 Hardhik 27 Bhopal 8500.00

SQL Less than (<) Operator

The less than operator in SQL is used to return rows having values less than the specified value. It checks if the left expression is less than the right expression. It is used to filter rows with values below a certain threshold.

Example

Now, we are retrieving all the customers whose salary is less than 5000.

SELECT * FROM customers WHERE salary < 5000;

Output

The table for the above query produced as given below −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
6 Komal 22 MP 4500.00

SQL Greater than or equal to (>=) Operator

The greater than or equal to operator in SQL is used to return rows having values either greater than or equal to the specified value. It checks if the left expression is greater than or equal to the right expression. It is used to filter rows with values equal to or above a certain limit.

Example

Now, we are retrieving all the customers whose salary is either greater than or equal to 4500.

SELECT * FROM customers WHERE salary >= 4500;

Output

Following is the output of the above query −

ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardhik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

SQL Less than or equal to (<=) Operator

The less than or equal to operator in SQL is used to return rows having values either less than or equal to the specified value. It checks if the left expression is less than or equal to the right expression. It is used to filter rows with values equal to or below a certain limit.

Example

Here, we are retrieving all the customers whose age is either less than or equal to 25.

SELECT * FROM customers WHERE age <= 25;

Output

The output produced is as shown below −

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

SQL Not less than (!<) Operator

The not less than operator in SQL is used to return rows having values not less than i.e. greater than or equal to the specified value. It checks if the left expression is greater than or equal to the right expression. It is used to filter rows with values equal to or above a certain limit.

Example

Here, we are retrieving all the customers whose age is not less than 25.

SELECT * FROM customers WHERE age !< 25;

Output

Output of the above code is as shown below −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00
5 Hardhik 27 Bhopal 8500.00

SQL Not greater than (!>) Operator

The not greater than operator in SQL is used to return rows having values not greater than i.e. less than or equal to the specified value. It checks if the left expression is less than or equal to the right expression. It is used to filter rows with values equal to or below a certain limit.

Example

Here, we are retrieving all the customers whose salary is not greater than 5000.

SELECT * FROM customers WHERE salary !> 5000;

Output

Output of the above code is as shown below −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
6 Komal 22 MP 4500.00
sql-operators.htm
Advertisements