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