SQL > SQL Commands > WHERE Clause

The WHERE clause is used to filter the result set based on the condition specified following the word WHERE.

The WHERE clause can be used with the following types of SQL statements:

Syntax

The syntax for using WHERE in the SELECT statement is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "condition";

The syntax for using WHERE in the UPDATE statement is as follows:

UPDATE "table_name"
SET "column_1" = [new value]
WHERE "condition";

The syntax for using WHERE in the DELETE statement is as follows:

DELETE FROM "table_name"
WHERE "condition";

"Condition" can include a single comparison clause (called simple condition) or multiple comparison clauses combined together using AND or OR operators (compound condition).

Examples

We provide examples here to show how to use WHERE in the SELECT statement.

Example 1: WHERE Clause With Simple Condition

To select all stores with sales above $1,000 in Table Store_Information,

Table Store_Information

 Store_Name  Sales  Txn_Date 
 Los Angeles  1500  Jan-05-1999 
 San Diego  250  Jan-07-1999 
 Los Angeles  300  Jan-08-1999 
 Boston  700  Jan-08-1999 

we key in,

SELECT Store_Name
FROM Store_Information
WHERE Sales > 1000;

Result:

Store_Name
Los Angeles

Example 2: WHERE Clause With OR Operator

To view all data with sales greater than $1,000 or with transaction date of 'Jan-08-1999', we use the following SQL,

SELECT *
FROM Store_Information
WHERE Sales > 1000 OR Txn_Date = 'Jan-08-1999';

Result:

Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999

The first returned row has sales greater than $1,000. The next two returned rows have a transaction date of 'Jan-08-1999.'

Using WHERE With UPDATE and DELETE

As mentioned above, the WHERE clause can be used with UPDATE and DELETE statements in addition to the SELECT statement. Examples of how to use the WHERE clause with these two commands can be seen in the UPDATE and DELETE sections.

Exercises

For these exercises, assume we have a table called Users with the following data:

Table Users

 First_Name  Last_Name  Birth_Date  Gender  Join_Date 
 Sophie  Lee  Jan-05-1960  F  Apr-05-2015 
 Richard  Brown  Jan-07-1975  M  Apr-05-2015 
 Jamal  Santo  Oct-08-1983  M  Apr-09-2015 
 Casey  Healy  Sep-20-1969  M  Apr-09-2015 
 Jill  Wilkes  Nov-20-1979  F  Apr-15-2015 

1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT * FROM Users WHERE Gender = 'M';
b) SELECT * WHERE Gender = 'M' FROM Users;
c) SELECT Gender= 'M' FROM Users;
d) SELECT Gender FROM Users WHERE Last_Name = 'Wilkes';

2. What's the result of the following query?
SELECT * FROM Users WHERE Join_Date = 'Apr-09-2015';

3. (True or False) The condition used in the WHERE clause must include a column that is part of the SELECT clause.

Next: SQL AND OR

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact