Basic about WHERE Clause – using and example

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 consequences and apply stipulations 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 prerequisites that should be met for documents to be selected.

DDL/DML for Examples

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

Get DDL/DML

Example – One Condition in the WHERE Clause

It is hard to give an explanation for the syntax for the SQL WHERE clause, so let’s begin with an instance that uses the WHERE clause to apply 1 condition.

In this example, we have a desk known as suppliers with the following data:

supplier_id supplier_name town 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:

Try It

SELECT *
FROM suppliers
WHERE state = 'California';

There will be four archives selected. These are the consequences that you must see:

supplier_id supplier_name metropolis 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 got used the SQL WHERE clause to filter our effects from the suppliers table. The SQL assertion above would return all rows from the suppliers desk where the state is California. Because the * is used in the select, all fields from the suppliers table would show up in the end result set.

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

You can use the AND situation in the WHERE clause to specify extra than 1 circumstance that need to be met for the record to be selected. Let’s discover how to do this.

In this example, we have a desk known as customers with the following data:

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

Now enter the following SQL statement:

Try It

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

There will be 1 file selected. These are the effects that you need to see:

customer_id last_name first_name favorite_website 9000 Johnson Derek techonthenet.com

This instance uses the WHERE clause to outline a couple of conditions. In this case, this SQL declaration uses the AND condition to return all clients whose favorite_website is techonthenet.com and whose customer_id is higher than 6000.

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

You can use the OR circumstance in the WHERE clause to check a couple of prerequisites where the document is back if any one of the prerequisites are met.

In this example, we have a table known as 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:

Try It

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

There will be 2 information selected. These are the consequences that you have to see:

product_id product_name category_id 1 Pear 50 4 Apple 50

This example uses the WHERE clause to define a couple of conditions, but alternatively of using the AND condition, it makes use of the OR condition. In this case, this SQL declaration would return all records from the merchandise desk where the product_name is either Pear or Apple.

Example – Combining AND & OR conditions

You can also combine the AND circumstance with the OR situation to take a look at greater complex conditions.

Let’s use the merchandise desk once more 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:

Try It

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

There will be two data selected. These are the outcomes that you should see:

product_id product_name category_id 1 Pear 50 5 Bread seventy five

This example would return all merchandise whose product_id is larger than 3 and category_id is seventy five as nicely as all products whose product_name is Pear.

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