Sql Comparison operator

Sql Comparison operator

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

Sql Less than ( < ) operator

 

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

Sql Grearter than or equal to ( >= ) operator

 

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

Sql Less than or equal to ( <= ) operator

 

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

Sql Not equal to ( <> ) operator

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database;