w3resource logo


Sql Comparison operator

Sql Comparison operator

<<PreviousNext>>

Seondary Nav

Description

A comparison (or relational) operator is a mathematical symbol which is used to compare between 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 (<=).

Table of Contents:

Example

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 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 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 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 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 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 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;



<<PreviousNext>>

Looking for some other tutorial?