SQL Where

The SQL WHERE clause is used to alter the behavior of a SELECT statement or other SQL commands that are applied to rows of data, in order to either restrict the rows returned by a query or the records affected by other statement types (DELETE, UPDATE, etc.) The SQL WHERE clause is the simplest clause to understand and without question one of the most frequently used.

Let’s return to looking at the employees table. In the previous section, we used a SELECT statement to return all rows from the table, as below:

SELECT * FROM employees;

However, this returns all rows from the table – and could be a very large result, as some databases have tables with millions (or even hundreds of millions!) of records. Furthermore, we would like to “drill-down” and look for more specific employees within the employees table matching specific criteria. 

So we will apply a WHERE clause here to alter the behavior of the SELECT statement. A WHERE clause comes after the SELECT statement and takes a form as below:

SELECT <columns> FROM <table> WHERE <condition>;

Where, in this form, <condition> must be a SQL expression which evaluates to either True or False for each record in the table – that is, a predicate.

The simplest expression we can write is to check whether a given column is equal to a value we specify using the equality operator (=). This will act as a filter and return only the rows which match the predicate we have written.

For example, let’s look at the employees table again, but only return employees with the first name ‘Georgi’:

SELECT * FROM employees WHERE first_name = "Georgi";

We can see above that our SQL query modified with a WHERE clause has only returned rows matching the predicate, and filtered the result set that is returned from the table specified.

Predicates need not be specified using only the equality operator nor using only text fields; we could also look for rows in the employees table where the hire date is greater than a certain value, using the greater than operator (>) and a date value:

SELECT * FROM employees WHERE hire_date > "1995-01-01";

It should also be noted that predicates need not be applied to fields which are returned in the SELECT statement and operate independently of them; for example, the below is a perfectly valid query with a WHERE clause and will return the result, filtering on the unseen first_name field which is not returned in the result:

SELECT last_name, gender, hire_date FROM employees WHERE first_name = "Georgi";

That being said, it is considered a best practice to include the fields used in the predicate for the WHERE clause in the SELECT which makes the logic of the query easier to read, and also to ensure that the desired result is being returned correctly. As such, the below would be preferred to the query above:

SELECT first_name, last_name, gender, hire_date FROM employees WHERE first_name = "Georgi";

In this case we would be able to examine the first_name field returned to verify that our predicate is returning the correct result, which would not be possible with the previous query.

Finally, the predicate for a WHERE clause can be any expression (or combination of expressions) as long it evaluates to True or False; this allows specifying detailed criteria for the result returned by a query through usage of logical operators such as AND and OR (covered in greater detail in a later section):

SELECT * FROM employees WHERE first_name = "Georgi" AND hire_date > "1995-01-01";

We can see how even simple SELECT statements combined with a single WHERE clause some predicates begin to unlock the true power of SQL to dive into data!