In this lesson, you will learn two important SQL concepts: the WHERE clause and the SELECT WHERE statement.

What is the SQL WHERE Clause?

The WHERE clause is close to the SELECT statement for the crown of 'the most important concepts in SQL'.

The WHERE clause allows you to specify conditions on columns, and only return rows that meet those conditions.

The WHERE clause appears immediately after the table name specified in the FROM clause of a SELECT statement.

The basic syntax of a WHERE clause is below. Note that, although we haven't seen it yet, it is perfectly fine to spread SQL queries across multiple lines (in fact, this is considered a best practice one your queries become sufficiently complex):

SELECT column_name

FROM table_name

WHERE conditions;

As before, we can specify multiple columns in the SELECT statement by separating them with commas, like this:

SELECT column_name1, column_name2

FROM table_name

WHERE conditions;

SQL contains a number of operators that we can use to specify the conditions on the last line. We will explore those operators in the next section of this lesson.

SQL Operators

In this section, we'll explore the various operators available to create conditions in a WHERE clause.

Comparison Operators

The comparison operators allow us to compare a column's value to an external data point. As an example, we could use comparison operators to ask "Is staff_id greater than or equal to 2?", or "Is title equal to Jurassic Park?".

The SQL comparison operators are the same operators that we use in basic math (just like in many other programming languages). They are shown below.

With this knowledge, we can now write actual SQL queries to answer the two questions I introduced previously!

Let's start with "Is staff_id greater than or equal to 2?", using the information from the payment table:

SELECT staff_id

FROM payment

WHERE staff_id >= 2;

Using the film table, we can answer the second question "Is title equal to Jurassic Park?" like this:

SELECT *

FROM film

WHERE title='Jurassic Park';

Note that unlike in many other programming languages, the " and ' characters are not equivalent in SQL. Strings must be wrapped in single-quotes. This is because the following code will actually return an error:

SELECT *

FROM film

WHERE title="Jurassic Park";

Logical Operators

Logical operators in SQL are very similar to their function in other programming languages (like Python). They allow you to chain together various conditions in the WHERE clause.

There are three logical operators in SQL:

• AND
• OR
• NOT

The AND operator allows us to chain together two conditions that must BOTH be met.

As an example, the following query searches for rows from the payment table where the price is between $1 and $5:

SELECT *

FROM payment

WHERE amount > 1 AND amount < 5;

The OR operator allows us to include two or more conditions, but only one of them needs to be satisfied for a row to be returned.

As an example, there is a table named staff that contains information about all of the staff that work in our DVD Rental store.

The following query returns rows for employees whose first name is either Mike or Jon:

SELECT * 

FROM staff

WHERE first_name='Mike' OR first_name='Jon';

Lastly, the NOT operator allows us to test that a certain column does not hold a particular value.

If we want to again consider the staff table, we could use the following query to get information on every employee whose first name is not Mike:

SELECT * 

FROM staff

WHERE NOT first_name='Mike'

Using The WHERE Clause on One Column, and Returning A Different Column

One very useful feature of the WHERE clause that we have not yet discussed in this lesson is the ability to place conditions on one column, and return a different column. We do this by placing different columns after the SELECT and WHERE keywords.

As an example, let's consider the film table within the DVD Rental database. Let's suppose that we want a list of every movie whose rating is R. In this case, it is not useful to return the rating column because we already know that every row should contain R. Instead, we want to return the title column, even though we are placing our condition on the rating column.

The following code solves this problem nicely:

SELECT title

FROM film

WHERE rating='R';

Final Thoughts

In this lesson, you learned how to use the WHERE clause along with the SELECT statement to create your first SQL SELECT WHERE query. In the next lesson, we will learn another extremely useful query syntax: the SQL ORDER BY statement.