Tagicon

How to Use Comparison Operators in SQL

Posted by tagicon_admin | in : SQL

What is Comparison Operators in SQL?

Basically comparison operators are used to compare one expression with another value or expression. This operators are used in WHERE clause. Compare data must be same data type. When we need specific data or filter data from data based then we can use this operators.

Now I am showing a list of comparison operators that are used in SQL:

–> = Equal
–> <> Not Equal
–> != Not Equal
–> ^= Not Equal
–> > Greater Than
–> >= Greater Than or Equal to
–> < Less Than
–> <= Less Than or Equal to
–> IN() Matches value from list
–> BETWEEN For range(inclusive)
–> IS NULL Is a null value
–> IS NOT NULL Non-NULL value

 

Syntax of Comparison Operators in SQL:

SELECT column_name, column_name
FROM table_name
WHERE column_name operator value;

Equal(=) Operator in SQL:

When we need specific data or test quality within two data, numbers or expressions then we use equal (=) operator.

Example:

 

SELECT last_name, email, phone_number
FROM employees
WHERE employee_id = 192;

Answer:

LAST_NAME                 EMAIL                     PHONE_NUMBER
------------------------- ------------------------- -------------
Bell                      SBELL                     650.501.1876

 

SQL Statement:

SELECT country_id, country_name 
FROM countries
WHERE region_id = 4;

Answer:

CO COUNTRY_NAME
-- -------------
EG Egypt
IL Israel
KW Kuwait
NG Nigeria
ZM Zambia
ZW Zimbabwe

SQL Statement:

SELECT department_name, department_id, manager_id
FROM departments
WHERE location_id = 1700;

Answer:

DEPARTMENT_NAME                DEPARTMENT_ID MANAGER_ID
------------------------------ ------------- ----------
Administration                            10        200
Purchasing                                30        114
Executive                                 90        100
Finance                                  100        108
Accounting                               110        205

 

SQL Statement:

SELECT employee_id, department_id, job_id, start_date
FROM job_history
WHERE end_date = '17-JUN-01';

Answer:

EMPLOYEE_ID DEPARTMENT_ID JOB_ID     START_DAT
----------- ------------- ---------- ---------
        200            90 AD_ASST    17-SEP-95

 

Not Equal(<>, !=, ^=) Operator in SQL:

We can easily identify our specific data by using Not Equal Operator. When we need to data that are not equal with other data than we can use this operator.

Example:

SELECT last_name, job_id, hire_date, salary
FROM employees
WHERE department_id <> 80;

Answer:

LAST_NAME                 JOB_ID     HIRE_DATE     SALARY
------------------------- ---------- --------- ----------
OConnell                  SH_CLERK   21-JUN-07       2600
Grant                     SH_CLERK   13-JAN-08       2600
Whalen                    AD_ASST    17-SEP-03       4400
Hartstein                 MK_MAN     17-FEB-04      13000
Fay                       MK_REP     17-AUG-05       6000
Mavris                    HR_REP     07-JUN-02       6500
Baer                      PR_REP     07-JUN-02      10000
Higgins                   AC_MGR     07-JUN-02      12008
Gietz                     AC_ACCOUNT 07-JUN-02       8300
King                      AD_PRES    17-JUN-03      24000

 

SQL Statement:

SELECT country_id, country_name
FROM countries
WHERE country_name<> 'United States of America'
AND region_id != 4;

Answer:

CO COUNTRY_NAME
-- ------------
AR Argentina
AU Australia
BE Belgium
BR Brazil
CA Canada
CH Switzerland
CN China
DE Germany
DK Denmark
FR France
IN India
IT Italy
JP Japan
ML Malaysia

 

SQL Statement:

SELECT department_name, department_id, manager_id
FROM departments
WHERE location_id ^= 1700;

Answer:

DEPARTMENT_NAME                DEPARTMENT_ID MANAGER_ID
------------------------------ ------------- ----------
Marketing                                 20        201
Human Resources                           40        203
Shipping                                  50        121
IT                                        60        103
Public Relations                          70        204
Sales                                     80        145

 

SQL Statement:

SELECT employee_id, last_name, email, hire_date
FROM employees
WHERE job_id <> 'ST_CLERK'
AND manager_id != 100
AND department_id ^= 50;

Answer:

EMPLOYEE_ID LAST_NAME                 EMAIL                     HIRE_DATE
----------- ------------------------- ------------------------- ---------
        200 Whalen                    JWHALEN                   17-SEP-03
        202 Fay                       PFAY                      17-AUG-05
        203 Mavris                    SMAVRIS                   07-JUN-02
        204 Baer                      HBAER                     07-JUN-02
        205 Higgins                   SHIGGINS                  07-JUN-02
        206 Gietz                     WGIETZ                    07-JUN-02
        103 Hunold                    AHUNOLD                   03-JAN-06
        104 Ernst                     BERNST                    21-MAY-07
        105 Austin                    DAUSTIN                   25-JUN-05
        106 Pataballa                 VPATABAL                  05-FEB-06

 

Greater Than (>) Operator in SQL:

When we need big value or greater value from expressions or data then we use greater than operator.

Example:

SELECT last_name, salary
FROM employees
WHERE salary > 10000;

Answer:

LAST_NAME                     SALARY
------------------------- ----------
Hartstein                      13000
Higgins                        12008
Test trigger                   50000
King                           24000
Kochhar                        17000
De Haan                        17000
Greenberg                      12008
Raphaely                       11000
Russell                        14000
Partners                       13500

 

SQL Statement:

SELECT department_name, department_id, manager_id
FROM departments
WHERE location_id > 1700;

Answer:

DEPARTMENT_NAME                DEPARTMENT_ID MANAGER_ID
------------------------------ ------------- ----------
Marketing                                 20        201
Human Resources                           40        203
Sales                                     80        145
Public Relations                          70        204

 

Greater Than or Equal to (>=) Operator in SQL :

When we need value that are greater than or equal from other expressions or values then we can use greater than or equal to operator.

Example:

SELECT last_name, salary
FROM employees
WHERE salary >= 17000;

Answer:

LAST_NAME                     SALARY
------------------------- ----------
Test trigger                   50000
King                           24000
Kochhar                        17000
De Haan                        17000

 

Less Than (<) Operator in SQL :

Less than operator is opposite of greater than operator. When we need small value from other expression, we can use less than operator.

Example:

SELECT location_id, city, street_address
FROM locations
WHERE location_id < 2000;

Answer:

LOCATION_ID CITY                           STREET_ADDRESS
----------- ------------------------------ ------------------------
       1000 Roma                           1297 Via Cola di Rie
       1100 Venice                         93091 Calle della Testa
       1200 Tokyo                          2017 Shinjuku-ku
       1300 Hiroshima                      9450 Kamiya-cho
       1400 Southlake                      2014 Jabberwocky Rd
       1500 South San Francisco            2011 Interiors Blvd
       1600 South Brunswick                2007 Zagora St
       1700 Seattle                        2004 Charade Rd
       1800 Toronto                        147 Spadina Ave
       1900 Whitehorse                     6092 Boxwood St

 

SQL Statement:

SELECT job_id, employee_id, department_id
FROM job_history
WHERE department_id < 80;

 

Answer:

JOB_ID     EMPLOYEE_ID DEPARTMENT_ID
---------- ----------- -------------
MK_REP             201            20
ST_CLERK           114            50
ST_CLERK           122            50
IT_PROG            102            60

 

Less Than or Equal to (<=) Operator in SQL :

Less than or equal to operator is opposite of greater than or equal to operator. We need small value or same value from other expression then we can use this operator.

Example:

SELECT last_name, employee_id
FROM employees
WHERE department_id <= 80;

Answer:

LAST_NAME                 EMPLOYEE_ID
------------------------- -----------
OConnell                          198
Grant                             199
Whalen                            200
Hartstein                         201
Fay                               202
Mavris                            203
Baer                              204
Hunold                            103

 

SQL Statement:

SELECT first_name, salary
FROM employees
WHERE salary <= 15000;

 

Answer:

FIRST_NAME               SALARY
-------------------- ----------
Donald                     2600
Douglas                    2600
Jennifer                   4400
Michael                   13000
Pat                        6000
Susan                      6500
Hermann                   10000
Shelley                   12008
William                    8300
Alexander                  9000
Bruce                      6000
David                      4800

 

IN() operator in SQL :

IN operator is used to find a set of value from database.

Example:

SELECT first_name, salary, job_id
FROM employees
WHERE department_id IN (50, 80);

 

Answer:

FIRST_NAME               SALARY JOB_ID
-------------------- ---------- ---------
Donald                     2600 SH_CLERK
Douglas                    2600 SH_CLERK
Matthew                    8000 ST_MAN
Adam                       8200 ST_MAN
Payam                      7900 ST_MAN
Shanta                     6500 ST_MAN
Kevin                      5800 ST_MAN
Julia                      3200 ST_CLERK
Irene                      2700 ST_CLERK
James                      2400 ST_CLERK

SQL Statement:

SELECT last_name, email, department_id
FROM employees
WHERE job_id IN ('SH_CLERK', 'ST_MAN');

 

Answer:

LAST_NAME                 EMAIL                     DEPARTMENT_ID
------------------------- ------------------------- -------------
OConnell                  DOCONNEL                             50
Grant                     DGRANT                               50
Weiss                     MWEISS                               50
Fripp                     AFRIPP                               50
Kaufling                  PKAUFLIN                             50
Vollman                   SVOLLMAN                             50
Mourgos                   KMOURGOS                             50
Taylor                    WTAYLOR                              50

 

BETWEEN Operator in SQL :

BETWEEN operators are used to select values within a range.

Example:

SELECT last_name, employee_id, salary
FROM employees
WHERE salary BETWEEN 5000 AND 10000;

Answer:

LAST_NAME                 EMPLOYEE_ID     SALARY
------------------------- ----------- ----------
Fay                               202       6000
Mavris                            203       6500
Baer                              204      10000
Gietz                             206       8300
Hunold                            103       9000
Ernst                             104       6000
Faviet                            109       9000
Chen                              110       8200
Sciarra                           111       7700
Urman                             112       7800

SQL Statement:

SELECT first_name, department_id, job_id
FROM employees
WHERE job_id BETWEEN 'ST_CLERK' AND 'ST_MAN';

 

Answer:

FIRST_NAME           DEPARTMENT_ID JOB_ID
-------------------- ------------- --------
Matthew                         50 ST_MAN
Adam                            50 ST_MAN
Payam                           50 ST_MAN
Shanta                          50 ST_MAN
Kevin                           50 ST_MAN
Julia                           50 ST_CLERK
Irene                           50 ST_CLERK
James                           50 ST_CLERK

 

IS NULL operator in SQL :

When we need data from database that are null then we can use IS NULL operator. IS NULL operator is used to identify null value.

Example:

SELECT last_name, job_id, commission_pct
FROM employees
WHERE commission_pct IS NULL;

 

Answer:

LAST_NAME                 JOB_ID     COMMISSION_PCT
------------------------- ---------- --------------
OConnell                  SH_CLERK
Grant                     SH_CLERK
Whalen                    AD_ASST
Hartstein                 MK_MAN
Fay                       MK_REP
Mavris                    HR_REP
Baer                      PR_REP
Higgins                   AC_MGR
Gietz                     AC_ACCOUNT

 

IS NOT NULL operator in SQL:

IS NOT NULL operator is opposite of IS NULL operator. IS NOT NULL operator is used to identify value that are not null.

Example:

SELECT last_name, job_id, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

 

Answer:

LAST_NAME                 JOB_ID     COMMISSION_PCT
------------------------- ---------- --------------
Russell                   SA_MAN                 .4
Partners                  SA_MAN                 .3
Errazuriz                 SA_MAN                 .3
Cambrault                 SA_MAN                 .3
Zlotkey                   SA_MAN                 .2
Tucker                    SA_REP                 .3
Bernstein                 SA_REP                .25
Hall                      SA_REP                .25

 

Leave a Reply