w3resource logo
SQL Tutorial

SQL Comparison operator

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
= Equal to.
> Greater than.
< Less than.
>= Greater than equal to.
<= Less than equal to.
<> Not equal to.

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:

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;

SQL Equal to ( = ) operator

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

Example:

Sample table: agents


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 :

SELECT *
FROM agents
WHERE commission = 0.15;

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

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 :

SELECT *
FROM agents
WHERE commission> 0.14;

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

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 :

SELECT *
FROM agents
WHERE commission < 0.12;

Output:

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

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 :

SELECT *
FROM agents
WHERE commission >= 0.14;

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

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 :

SELECT *
FROM agents
WHERE commission <= 0.12;

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

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 :

SELECT *
FROM agents
WHERE commission <> 0.15;

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

See our Model Database