w3resource

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.

SQL: WHERE clause - Syntax diagram

SQL WHERE clause syntax

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

SQL: Comparison condition - Syntax diagram

SQL Comparison condition syntax

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:

-- Selecting specific columns: employee_id, first_name, last_name, department_id
-- From the 'employees' table
SELECT employee_id, first_name, last_name, department_id 
FROM employees
-- Filtering the results to include only rows where the 'department_id' is equal to 100
-- From the 'employees' table
WHERE department_id = 100;

Relational Algebra Expression:

Relational Algebra Expression: Using the WHERE clause in SQL.

Relational Algebra Tree:

Relational Algebra Tree: Using the WHERE clause in SQL.

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

Visual 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:

-- Selecting specific columns: employee_id, job_id, salary
SELECT employee_id, job_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'last_name' is equal to 'Lorentz'
WHERE last_name = 'Lorentz';

Relational Algebra Expression:

Relational Algebra Expression: Using the WHERE clause in SQL.

Relational Algebra Tree:

Relational Algebra Tree: Using the WHERE clause in SQL.

Output:

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

Visual 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:

-- Selecting specific columns from the 'employees' table
SELECT employee_id, first_name, last_name, salary
-- Filtering the results to include only rows where the 'salary' is greater than or equal to 4000
FROM employees
WHERE salary >= 4000;

Relational Algebra Expression:

Relational Algebra Expression: WHERE clause using comparison conditions in SQL.

Relational Algebra Tree:

Relational Algebra Tree: WHERE clause using comparison conditions in SQL.

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:

-- Selecting specific columns from the 'employees' table: first_name, last_name, salary
-- Calculating the "Net Salary" by adding the commission to the salary
SELECT first_name, last_name, salary, (salary + (salary * commission_pct)) "Net Salary"
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the "Net Salary" is between 10000 and 15000
-- and the commission_pct is greater than 0
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

Visual 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.

SQL: BETWEEN condition - Syntax diagram

SQL BETWEEN condition syntax

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:

-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, salary
SELECT employee_id, first_name, last_name, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'salary' is between 4000 and 6000
WHERE salary BETWEEN 4000 AND 6000;

Relational Algebra Expression:

Relational Algebra Expression: WHERE clause using BETWEEN condition in SQL.

Relational Algebra Tree:

Relational Algebra Tree: WHERE clause using BETWEEN condition in SQL.

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

Visual 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.

SQL: IN condition - Syntax diagram

SQL IN condition syntax

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:

-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'department_id' is in the list (60, 90, 100)
WHERE department_id IN (60, 90, 100);

Relational Algebra Expression:

Relational Algebra Expression: WHERE clause using IN condition in SQL.

Relational Algebra Tree:

Relational Algebra Tree: WHERE clause using IN condition in SQL.

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

Visual 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.

SQL: LIKE condition - Syntax diagram

SQL LIKE condition syntax

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:

-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'first_name' starts with the letter 'S'
WHERE first_name LIKE ('S%');

Relational Algebra Expression:

Relational Algebra Expression: WHERE clause using LIKE condition in SQL.

Relational Algebra Tree:

Relational Algebra Tree: WHERE clause using LIKE condition in SQL.

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

Visual presentation :

SQL: WHERE clause using LIKE condition

Example : WHERE clause using NULL condition in SQL

IS NULL operator is used to test for nulls.

SQL: NULL condition - Syntax diagram

SQL NULL condition syntax

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:

-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'department_id' is NULL
WHERE department_id IS NULL;

Relational Algebra Expression:

Relational Algebra Expression: WHERE clause using NULL condition in SQL.

Relational Algebra Tree:

Relational Algebra Tree: WHERE clause using NULL condition in SQL.

Output:

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

Visual 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.

SQL: Logical condition - Syntax diagram

SQL Logical condition syntax

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:

-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'first_name' starts with the letter 'S'
-- and the 'salary' is greater than or equal to 4000
WHERE first_name LIKE ('S%') AND salary >= 4000;

Relational Algebra Expression:

Relational Algebra Expression: WHERE clause using the AND operator  in SQL.

Relational Algebra Tree:

Relational Algebra Tree: WHERE clause using the AND operator  in SQL.

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:

-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'first_name' starts with the letter 'S'
-- OR the 'first_name' starts with the letter 'A'
WHERE first_name LIKE ('S%') OR first_name LIKE ('A%');

Relational Algebra Expression:

Relational Algebra Expression: WHERE clause using the OR operator  in SQL.

Relational Algebra Tree:

Relational Algebra Tree: WHERE clause using the OR operator  in SQL.

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:

-- Selecting specific columns from the 'employees' table: employee_id, first_name, last_name, department_id, salary
SELECT employee_id, first_name, last_name, department_id, salary
-- From the 'employees' table
FROM employees
-- Filtering the results to include only rows where the 'department_id' is NOT IN (90, 60, 100)
WHERE department_id NOT IN (90, 60, 100);

Relational Algebra Expression:

Relational Algebra Expression: WHERE clause using the NOT operator  in SQL.

Relational Algebra Tree:

Relational Algebra Tree: WHERE clause using the NOT operator  in SQL.

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

Visual presentation :

SQL: WHERE clause using the NOT operator

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SELECT with DISTINCT Multiple Columns
Next: SQL Operators



Follow us on Facebook and Twitter for latest update.