SQL Comparison operator

Last update on April 20 2024 12:31:58 (UTC/GMT +8 hours)

Comparison operator

A comparison (or relational) operator is a mathematical symbol which is used to compare two values.

Comparison operators are used in conditions that compares one expression with another. The result of a comparison can be TRUE, FALSE, or UNKNOWN (an operator that has one or two NULL expressions returns UNKNOWN).

The following table describes different types of comparison operators -

Operator	Description	Operates on
=	Equal to.	Any compatible data types
>	Greater than.	Any compatible data types
<	Less than.	Any compatible data types
>=	Greater than equal to.	Any compatible data types
<=	Less than equal to.	Any compatible data types
<>	Not equal to.	Any compatible data types

Syntax :

SELECT[column_name| * |expression]<comparison operator>
[column_name | * | expression ]
FROM <table_name>
WHERE <expression>[comparison operator]<expression>;

Parameters:

Name	Description
column_name	Name of the column of a table.
*	Indicates all the columns of a table.
expression	Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name	Name of the table.
comparison operator	Equal to (=), not equal to(<>), greater than(>), less than(<), greater than or equal to (>=), less than or equal to (<=).

Contents:

Equal to Operator
Greater than Operator
Less than Operator
Greater than or equal to Operator
Less than or equal to Operator
Not equal to Operator

Example: SQL Comparison operator

To get a comparison between two numbers from the DUAL table, the following SQL statement can be used :


SELECT 15>14 FROM dual;

Explanation:

SELECT: This keyword is used to retrieve data from a database.
15>14: This is a boolean expression that evaluates to true (1 in SQL) because 15 is greater than 14.
FROM dual: The dual table is a special one-row, one-column table in Oracle database. It's commonly used in SQL queries to perform calculations or evaluate expressions. In this case, it's used to execute the boolean expression 15>14 and return the result.

SQL Equal to ( = ) operator

The equal to operator is used for equality test within two numbers or expressions.

Example:

Sample table : agents

 
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To get data of all columns from the 'agents' table with the following condition -

1. 'commission' is equal to .15,

the following SQL statement can be used :

SQL Code:


SELECT *   -- Select all columns
FROM agents -- From the table named "agents"
WHERE commission = 0.15; -- Where the value in the column "commission" is equal to 0.15

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE commission = 0.15: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is equal to 0.15 should be included in the result set.

Output:

AGENT_ AGENT_NAME                     WORKING_AREA      COMMISSION PHONE_NO        COUNTRY
------ ------------------------------ ----------------- ---------- --------------- --------
A007   Ramasundar                     Bangalore                .15 077-25814763
A011   Ravi Kumar                     Bangalore                .15 077-45625874
A006   McDen                          London                   .15 078-22255588
A004   Ivan                           Torento                  .15 008-22544166

Relational Algebra Expression:

Relational Algebra Tree:

SQL Greater than ( > ) operator

The greater than operator is used to test whether an expression (or number) is greater than another one.

Example:

To get data of all columns from the 'agents' table with the following condition -

1. 'commission' is greater than .14,

the following SQL statement can be used :

SQL Code:


SELECT *   -- Select all columns
FROM agents -- From the table named "agents"
WHERE commission > 0.14; -- Where the value in the column "commission" is greater than 0.14

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE commission > 0.14: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is greater than 0.14 should be included in the result set.

Output:

AGENT_ AGENT_NAME     WORKING_AREA                        COMMISSION PHONE_NO        COUNTRY
------ -------------- ----------------------------------- ---------- --------------- -------
A007   Ramasundar     Bangalore                                  .15 077-25814763
A011   Ravi Kumar     Bangalore                                  .15 077-45625874
A006   McDen          London                                     .15 078-22255588
A004   Ivan           Torento                                    .15 008-22544166

Relational Algebra Expression:

Relational Algebra Tree:

SQL Less than ( < ) operator

The less than operator is used to test whether an expression (or number) is less than another one.

Example:

To get data of all columns from the 'agents' table with the following condition -

1. 'commission' is less than .12,

the following SQL statement can be used :

SQL Code:


SELECT *   -- Select all columns
FROM agents -- From the table named "agents"
WHERE commission < 0.12; -- Where the value in the column "commission" is less than 0.12

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE commission < 0.12: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is less than 0.12 should be included in the result set.

Output:

AGENT_ AGENT_NAME     WORKING_AREA                        COMMISSION PHONE_NO        COUNTRY
------ -------------- ----------------------------------- ---------- --------------- ---------
A009   Benjamin       Hampshair                                  .11 008-22536178
A002   Mukesh         Mumbai                                     .11 029-12358964

Relational Algebra Expression:

Relational Algebra Tree:

SQL Greater than or equal to ( >= ) operator

The greater than equal to operator is used to test whether an expression (or number) is either greater than or equal to another one.

Example:

To get data of all columns from the 'agents' table with the following condition -

1. 'commission' is greater than or equal to .14,

the following SQL statement can be used :

SQL Code:


SELECT *   -- Select all columns
FROM agents -- From the table named "agents"
WHERE commission >= 0.14; -- Where the value in the column "commission" is greater than or equal to 0.14

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE commission >= 0.14: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is greater than or equal to 0.14 should be included in the result set.

Output:

AGENT_ AGENT_NAME     WORKING_AREA                        COMMISSION PHONE_NO        COUNTRY
------ --------------- ----------------------------------- ---------- --------------- --------
A001   Subbarao       Bangalore                                  .14 077-12346674
A007   Ramasundar     Bangalore                                  .15 077-25814763
A011   Ravi Kumar     Bangalore                                  .15 077-45625874
A010   Santakumar     Chennai                                    .14 007-22388644
A006   McDen          London                                     .15 078-22255588
A004   Ivan           Torento                                    .15 008-22544166

Relational Algebra Expression:

Relational Algebra Tree:

SQL Less than or equal to ( <= ) operator

The less than equal to operator is used to test whether an expression (or number) is either less than or equal to another one.

Example:

To get data of all columns from the 'agents' table with the following condition -

1. commission is less than or equal to .12,

the following SQL statement can be used :

SQL Code:


SELECT *   -- Select all columns
FROM agents -- From the table named "agents"
WHERE commission <= 0.12; -- Where the value in the column "commission" is less than or equal to 0.12

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE commission <= 0.12: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is less than or equal to 0.12 should be included in the result set.

Output:

AGENT_ AGENT_NAME      WORKING_AREA                        COMMISSION PHONE_NO        COUNTRY
------ --------------- ---------------------------------- ---------- --------------- ---------
A009   Benjamin        Hampshair                                  .11 008-22536178
A008   Alford          New York                                   .12 044-25874365
A012   Lucida          San Jose                                   .12 044-52981425
A002   Mukesh          Mumbai                                     .11 029-12358964

Relational Algebra Expression:

Relational Algebra Tree:

SQL Not equal to ( <> ) operator

The not equal to operator is used for inequality test between two numbers or expression.

Example:

To get data of all columns from the 'agents' table with the following condition -

1. commission is not equal to .15,

the following SQL statement can be used :

SQL Code:


SELECT *   -- Select all columns
FROM agents -- From the table named "agents"
WHERE commission <> 0.15; -- Where the value in the column "commission" is not equal to 0.15

Explanation:

SELECT *: This specifies that we want to retrieve all columns from the table.
FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
WHERE commission <> 0.15: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is not equal to 0.15 should be included in the result set.

Output:

AGENT_ AGENT_NAME    WORKING_AREA                        COMMISSION PHONE_NO        COUNTRY
------ -------------  ----------------------------------- ---------- --------------- --------
A003   Alex          London                                     .13 075-12458969
A001   Subbarao      Bangalore                                  .14 077-12346674
A009   Benjamin      Hampshair                                  .11 008-22536178
A008   Alford        New York                                   .12 044-25874365
A010   Santakumar    Chennai                                    .14 007-22388644
A012   Lucida        San Jose                                   .12 044-52981425
A005   Anderson      Brisban                                    .13 045-21447739
A002   Mukesh        Mumbai                                     .11 029-12358964

Relational Algebra Expression:

Relational Algebra Tree:

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.