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
Contents:
- Types of conditions
- Example: Using the WHERE clause
- Example : WHERE clause using comparison conditions
- Example: WHERE clause using expression
- Example: WHERE clause using BETWEEN condition
- Example: WHERE clause using IN condition
- Example: WHERE clause using LIKE condition
- Example: WHERE clause using NULL condition
- Example: WHERE clause using Logical Conditions
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
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;
Relational Algebra Expression:
Relational Algebra Tree:
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 :
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';
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID JOB_ID SALARY
----------- ---------- ----------
107 IT_PROG 4200
Pictorial presentation :
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;
Relational Algebra Expression:
Relational Algebra Tree:
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 :
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
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;
Relational Algebra Expression:
Relational Algebra Tree:
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 :
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
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);
Relational Algebra Expression:
Relational Algebra Tree:
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 :
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
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%');
Relational Algebra Expression:
Relational Algebra Tree:
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 :
Example : WHERE clause using NULL condition in SQL
IS NULL operator is used to test for nulls.
SQL: NULL condition - Syntax diagram
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;
Relational Algebra Expression:
Relational Algebra Tree:
Output:
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID SALARY
----------- -------------------- ------------------------- ------------- ----------
178 Kimberely Grant 7000
Pictorial presentation :
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
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;
Relational Algebra Expression:
Relational Algebra Tree:
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%')
Relational Algebra Expression:
Relational Algebra Tree:
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);
Relational Algebra Expression:
Relational Algebra Tree:
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 :
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SELECT with DISTINCT Multiple Columns
Next: SQL Operators
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join