SQL: WHERE Clause

This SQL tutorial explains how to use the SQL WHERE clause with syntax and examples.

Description

The SQL WHERE clause is used to filter the results and apply conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the WHERE clause in SQL is:

WHERE conditions;

Parameters or Arguments

conditions

The conditions that must be met for records to be selected.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - One Condition in the WHERE Clause

It is difficult to explain the syntax for the SQL WHERE clause, so let's start with an example that uses the WHERE clause to apply 1 condition.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE state = 'California';

There will be 4 records selected. These are the results that you should see:

supplier_id	supplier_name	city	state
200	Google	Mountain View	California
300	Oracle	Redwood City	California
700	Dole Food Company	Westlake Village	California
900	Electronic Arts	Redwood City	California

In this example, we've used the SQL WHERE clause to filter our results from the suppliers table. The SQL statement above would return all rows from the suppliers table where the state is California. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

Example - Two Conditions in the WHERE Clause (AND Condition)

You can use the AND condition in the WHERE clause to specify more than 1 condition that must be met for the record to be selected. Let's explore how to do this.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

Now enter the following SQL statement:

SELECT *
FROM customers
WHERE favorite_website = 'google.com'
AND customer_id > 6000;

There will be 1 record selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
9000	Johnson	Derek	techonthenet.com

This example uses the WHERE clause to define multiple conditions. In this case, this SQL statement uses the AND condition to return all customers whose favorite_website is techonthenet.com and whose customer_id is greater than 6000.

Example - Two Conditions in the WHERE Clause (OR Condition)

You can use the O                                                   R condition in the WHERE clause to test multiple conditions where the record is returned if any one of the conditions are met.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Now enter the following SQL statement:

SELECT *
FROM products
WHERE product_name = 'Pear'
OR product_name = 'Apple';

There will be 2 records selected. These are the results that you should see:

product_id	product_name	category_id
1	Pear	50
4	Apple	50

This example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition. In this case, this SQL statement would return all records from the products table where the product_name is either Pear or Apple.

Example - Combining AND & OR conditions

You can also combine the AND condition with the OR condition to test more complex conditions.

Let's use the products table again for this example.

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Now enter the following SQL statement:

SELECT *
FROM products
WHERE (product_id > 3 AND category_id = 75)
OR (product_name = 'Pear');

There will be 2 records selected. These are the results that you should see:

product_id	product_name	category_id
1	Pear	50
5	Bread	75

This example would return all products whose product_id is greater than 3 and category_id is 75 as well as all products whose product_name is Pear.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!