w3resource logo
SQL Tutorial

SQL WHERE clause

Where clause

The basic form of the SELECT statement is SELECT-FROM-WHERE block. In a SELECT statement, WHERE clause is optional. Using SELECT without a WHERE clause is useful for browsing data from tables.

In a WHERE clause, you can specify a search condition (logical expression) that has one or more conditions. When the condition (logical expression) evaluates to true the WHERE clause filter unwanted rows from the result.

Here is the syntax :

Syntax:

SELECT <column_list> 
FROM < table name >
WHERE <condition>;

Parameters:

Name Description
table_name Name of the table.
column_list Name of the columns of the table.
condition Search condition.

Contents:

Types of conditions

Condition SQL Operators
Comparison =, >, >=, <, <=, <>
Range filtering BETWEEN
Match a character pattern LIKE
List filtering [Match any of a list of values] IN
Null testing IS NULL

Comparison Operators :

SQL Operators Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to

Example: Using the WHERE clause in SQL

The following query display the employee_id, first_name, last_name, department_id of employees whose departmet_id=100 :

Sample table : employees


SQL Code:

SELECT employee_id, first_name, 
last_name, department_id
FROM employees 
WHERE department_id=100;

Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
----------- -------------------- ------------------------- -------------
        108 Nancy                Greenberg                           100
        109 Daniel               Faviet                              100
        110 John                 Chen                                100
        111 Ismael               Sciarra                             100
        112 Jose Manuel          Urman                               100
        113 Luis                 Popp                                100

Pictorial presentation :

SQL WHERE CLAUSE

The following query displays the employee_id, job_id, salary of employees whose last_name='Lorentz'.
Note : Character strings are enclosed in quotation marks. Character values are case-sensitive for some database.

SQL Code:

SELECT employee_id, job_id, salary
FROM employees
WHERE last_name = 'Lorentz';

Output:

EMPLOYEE_ID JOB_ID         SALARY
----------- ---------- ----------
        107 IT_PROG          4200

Pictorial presentation :

Using WHERE CLAUSE in SQL

Example: WHERE clause using comparison conditions in SQL

The following query displays the employee_id, first_name, last_name and salary of employees whose salary is greater than or equal to 4000 :

Sample table : employees


SQL Code:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary>=4000;

Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24000
        101 Neena                Kochhar                        17000
        102 Lex                  De Haan                        17000
        103 Alexander            Hunold                          9000
        104 Bruce                Ernst                           6000
        105 David                Austin                          4800
        106 Valli                Pataballa                       4800
        107 Diana                Lorentz                         4200
        108 Nancy                Greenberg                      12008
        109 Daniel               Faviet                          9000
        110 John                 Chen                            8200
        111 Ismael               Sciarra                         7700
        112 Jose Manuel          Urman                           7800
        113 Luis                 Popp                            6900
        114 Den                  Raphaely                       11000
.........................
.........................

Example: WHERE clause using expression in SQL

The following query displays the first_name, last_name , salary and (salary+(salary*commission_pct)) as Net Salary of employees whose Net Salary is in the range 10000 and 15000 and who gets atleast a percentage of commission_pct.

Sample table: employees


SQL Code:

SELECT first_name,last_name,salary,
(salary+(salary*commission_pct)) "Net Salary" 
FROM employees 
WHERE
(salary+(salary*commission_pct)) 
BETWEEN 10000 AND 15000 
AND commission_pct>0

Output:

FIRST_NAME           LAST_NAME                     SALARY Net Salary
-------------------- ------------------------- ---------- ----------
Gerald               Cambrault                      11000      14300
Eleni                Zlotkey                        10500      12600
Peter                Tucker                         10000      13000
David                Bernstein                       9500      11875
Peter                Hall                            9000      11250
Janette              King                           10000      13500
Patrick              Sully                           9500      12825
Allan                McEwen                          9000      12150
Lindsey              Smith                           8000      10400
Clara                Vishney                        10500      13125
Danielle             Greene                          9500      10925
Lisa                 Ozer                           11500      14375
Harrison             Bloom                          10000      12000
Tayler               Fox                             9600      11520
Ellen                Abel                           11000      14300
Alyssa               Hutton                          8800      11000
Jonathon             Taylor                          8600      10320
Jack                 Livingston                      8400      10080

Pictorial presentation :

WHERE clause using expression in SQL

Example: WHERE clause using BETWEEN condition in SQL

The BETWEEN condition is used to test for values in a list.

The following query displays the employee_id, first_name, last_name and salary of employees whose salary is greater than or equal to 4000 and less than equal to 6000 where 4000 is thelower limit and 6000 is the upper limit of the salary.

Sample table : employees


SQL Code:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 4000 AND 6000;

Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        104 Bruce                Ernst                           6000
        105 David                Austin                          4800
        106 Valli                Pataballa                       4800
        107 Diana                Lorentz                         4200
        124 Kevin                Mourgos                         5800
        184 Nandita              Sarchand                        4200
        185 Alexis               Bull                            4100
        192 Sarah                Bell                            4000
        200 Jennifer             Whalen                          4400
        202 Pat                  Fay                             6000

Pictorial presentation :

WHERE clause using BETWEEN condition in SQL

Example: WHERE clause using IN condition in SQL

The IN condition is used to test for values in a list.

The following query displays the employee_id, first_name, last_name, department_id and salary of employees whose department_id 60, 90 or 100.

Sample table : employees


SQL Code:

SELECT employee_id, first_name, last_name, 
department_id, salary 
FROM employees
WHERE department_id IN(60,90,100);

Output :

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID     SALARY
----------- -------------------- ------------------------- ------------- ----------
        103 Alexander            Hunold                               60       9000
        104 Bruce                Ernst                                60       6000
        105 David                Austin                               60       4800
        106 Valli                Pataballa                            60       4800
        107 Diana                Lorentz                              60       4200
        100 Steven               King                                 90      24000
        101 Neena                Kochhar                              90      17000
        102 Lex                  De Haan                              90      17000
        108 Nancy                Greenberg                           100      12008
        109 Daniel               Faviet                              100       9000
        110 John                 Chen                                100       8200
        111 Ismael               Sciarra                             100       7700
        112 Jose Manuel          Urman                               100       7800
        113 Luis                 Popp                                100       6900

Pictorial presentation :

SQL: WHERE clause using IN condition

Example: WHERE clause using LIKE condition in SQL

The LIKE condition is used to perform wildcard searches of string values. The search condition can contain either numbers or literal characters, _ denotes one character and % denotes zero or many characters.

The following query displays the employee_id, first_name, last_name and salary of employees whose first_name starting with 'S'.

Sample table : employees


SQL Code:

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE first_name LIKE('S%');

Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID     SALARY
----------- -------------------- ------------------------- ------------- ----------
        166 Sundar               Ande                                 80       6400
        116 Shelli               Baida                                30       2900
        192 Sarah                Bell                                 50       4000
        205 Shelley              Higgins                             110      12008
        100 Steven               King                                 90      24000
        173 Sundita              Kumar                                80       6100
        128 Steven               Markle                               50       2200
        203 Susan                Mavris                               40       6500
        194 Samuel               McCain                               50       3200
        161 Sarath               Sewall                               80       7000
        138 Stephen              Stiles                               50       3200
        117 Sigal                Tobias                               30       2800
        123 Shanta               Vollman                              50       6500

Pictorial presentation :

SQL: WHERE clause using LIKE condition

Example : WHERE clause using NULL condition in SQL

IS NULL operator is used to test for nulls.

The following query displays the employee_id, first_name, last_name and salary of employees whose department_id is null.

Sample table : employees


SQL Code:

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE department_id IS NULL;

Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID     SALARY
----------- -------------------- ------------------------- ------------- ----------
        178 Kimberely            Grant                                         7000

Pictorial presentation :

SQL: WHERE clause using NULL condition

Example : WHERE clause using Logical Conditions in SQL

Logical Conditions

Operators Description
AND Returns TRUE if both conditions are true.
OR Returns TRUE if either condition is true.
NOT Returns TRUE if the following condition is false.

Example : WHERE clause using the AND operator in SQL

The following query displays the employee_id, first_name, last_name and salary of employees whose first_name starting with 'S' and salary greater than or equal to 4000.

Sample table : employees


SQL Code:

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE first_name LIKE('S%')
AND salary>=4000;

Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID     SALARY
----------- -------------------- ------------------------- ------------- ----------
        166 Sundar               Ande                                 80       6400
        192 Sarah                Bell                                 50       4000
        205 Shelley              Higgins                             110      12008
        100 Steven               King                                 90      24000
        173 Sundita              Kumar                                80       6100
        203 Susan                Mavris                               40       6500
        161 Sarath               Sewall                               80       7000
        123 Shanta               Vollman                              50       6500

Example: WHERE clause using the OR operator in SQL

The following query displays the employee_id, first_name, last_name and salary of employees whose first_name starting with 'S' or 'A'.

Sample table: employees


SQL Code:

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE first_name LIKE('S%')
OR first_name LIKE('A%')

Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID     SALARY
----------- -------------------- ------------------------- ------------- ----------
        100 Steven               King                                 90      24000
        103 Alexander            Hunold                               60       9000
        115 Alexander            Khoo                                 30       3100
        116 Shelli               Baida                                30       2900
        117 Sigal                Tobias                               30       2800
        121 Adam                 Fripp                                50       8200
        123 Shanta               Vollman                              50       6500
        128 Steven               Markle                               50       2200
        138 Stephen              Stiles                               50       3200
        147 Alberto              Errazuriz                            80      12000
        158 Allan                McEwen                               80       9000
        161 Sarath               Sewall                               80       7000
        166 Sundar               Ande                                 80       6400
        167 Amit                 Banda                                80       6200
        173 Sundita              Kumar                                80       6100
        175 Alyssa               Hutton                               80       8800
        185 Alexis               Bull                                 50       4100
        187 Anthony              Cabrio                               50       3000
        192 Sarah                Bell                                 50       4000
        194 Samuel               McCain                               50       3200
        196 Alana                Walsh                                50       3100
        203 Susan                Mavris                               40       6500
        205 Shelley              Higgins                             110      12008

Example: WHERE clause using the NOT operator in SQL

The following query displays the employee_id, first_name, last_name and salary of employees except the department_id 90, 60 or 100 :

Sample table: employees


SQL Code:

SELECT employee_id, first_name, last_name, 
department_id, salary
FROM employees
WHERE department_id
NOT IN (90, 60, 100);

Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID     SALARY
----------- -------------------- ------------------------- ------------- ----------
        114 Den                  Raphaely                             30      11000
        115 Alexander            Khoo                                 30       3100
        116 Shelli               Baida                                30       2900
        117 Sigal                Tobias                               30       2800
        118 Guy                  Himuro                               30       2600
        119 Karen                Colmenares                           30       2500
        120 Matthew              Weiss                                50       8000
        121 Adam                 Fripp                                50       8200
        122 Payam                Kaufling                             50       7900
        123 Shanta               Vollman                              50       6500
        124 Kevin                Mourgos                              50       5800
        125 Julia                Nayer                                50       3200
        126 Irene                Mikkilineni                          50       2700
        127 James                Landry                               50       2400
        128 Steven               Markle                               50       2200
        129 Laura                Bissot                               50       3300
        130 Mozhe                Atkinson                             50       2800
        131 James                Marlow                               50       2500
        132 TJ                   Olson                                50       2100
        133 Jason                Mallin                               50       3300
        134 Michael              Rogers                               50       2900
        135 Ki                   Gee                                  50       2400
        136 Hazel                Philtanker                           50       2200
        137 Renske               Ladwig                               50       3600
................
................		

Pictorial presentation :

SQL: WHERE clause using the NOT operator

See our Model Database