When querying a relational database, database programmers and administrators will often need to filter the records returned or extract specific records that meet a given criteria. In this database programming tutorial, we will look at the SQL WHERE clause, learn its syntax, and preview several code examples showing its use.
Read: How to Use the SQL SELECT Statement
SQL WHERE Clause
As stated in the intro, the SQL WHERE clause is used when we need to filter the records returned from a table to show a result based on a given condition or set of criteria. The syntax for the WHERE clause is:
SELECT column1, column2, column3 FROM name-of-table WHERE condition
In addition to being used alongside the SELECT statement, the SQL WHERE clause can be used with UPDATE and DELETE statements as well.
SQL WHERE Clause Code Example
For our first code example, let’s assume that we have a database with a table named Superheroes that contains the HeroNames, HeroID, and SuperPowers of known super heroes. As a sample dataset, let’s say the following entries exist in our Superheroes table:
HeroNames SuperPowers HeroID AwesomePossum Play dead 110 Whatawoman Jump rope of doom 120 TheSpoiler Ruins movie endings in a single bound 130
If we want to only pull up the record for super heroes whose name is AwesomePossum, we would write the follow SQL query, which shows how to use the WHERE clause:
SELECT * FROM Superheroes WHERE HeroNames'AwesomePossum;'
Running this query would result in the following output:
AwesomePossum Play dead 110
The above SQL example showed us how to use the WHERE clause to query a condition based on a text value – hence the inclusion of single quotation marks in our query (do not use double quotations!). If we want to query a numeric value using the WHERE clause, we would omit the single quotation marks, as shown in the following SQL example:
SELECT * FROM Superheroes WHERE HeroID=110;
Running this query will give us the result:
AwesomePossum Play dead 110
Read: How to Use SQL SELECT DISTINCT Statement
SQL WHERE Clause Operators
Of course, using only the equals or = operator to determine criteria in an SQL WHERE clause is very limiting. Fortunately, there are a number of other operators you can use in conjunction with the WHERE clause to set query criteria. SQL WHERE clause operators include:
- = Signifies values must equal x
- > Signifies values must be greater than x
- < Signifies values must be less than x
- >= Signifies values must be greater than or equal to x
- <= Signifies values must be less than or equal to x
- <> Signifies values must not be equal to x
- BETWEEN Signifies values must be within a range
- IN Used to specifiy more than one possible value in a column
- LIKE Used for searching a given pattern
SQL BETWEEN Operator Example
To use the BETWEEN operator with a WHERE clause on our example table, we would write the following SQL statement:
SELECT * FROM Superheroes WHERE HeroID BETWEEN 110 and 115;
Executing this SQL query would result in the output:
AwesomePossum Play dead 110
SQL IN Operator Example
Here is an example of how to use the IN operator with a WHERE clause in SQL:
SELECT * FROM Superheroes
WHERE HeroNames ('AwesomePossum', 'Whatawoman');
Running the above query on our sample dataset would return the data for the AwesomePossum and Whatawoman records.
SQL LIKE Operator Example
Here is an example showing how to use the LIKE operator with a WHERE clause in SQL:
SELECT * FROM Superheroes WHERE HeroNames LIKE 'A%';
In this example SQL query, we are searching the Superheroes table for any records where HeroNames start with A. The % symbol is used to say that any value after A is okay. Using our dataset sample, the result of running this query would be:
AwesomePossum Play dead 110
Had their been another hero with a name that began with A, it, too, would have been returned.
