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:

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.

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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 −