6. The Where Clause

Now we know how to select values from some or all of the columns in a single table. This is useful, but what if we only want to return some of the rows from our table rather than all of them? It's time for the where clause.

Where Clause Basics

The where clause is optional in all databases. However, in practice, virtually every query you write will contain one. where clauses limit the set of rows that are returned by your query. For example, let's say we want to find all donors whose first name is Mary. We can use the where clause for this:

select
    account_id,
    first_name,
    last_name
from accounts
where first_name = 'Mary'

By now, you should be familiar with what the select and from portions of this query are doing. The where clause limits the scope of the from clause to only the subset of rows where first_name is Mary. Note that the word Mary is enclosed in single quotes ('). Any time we need the database to evaluate a literal text or date value (as opposed to a column name or table name), we enclose it in single quotes (numeric literal values do not need quotes). Let's try it in the console.

Notice that every single row in our set of results has a first_name of Mary.

Case Sensitivity

Most databases (Microsoft SQL Server is a notable exception, which backs Raiser's Edge and EveryAction pipeline) compare text on a case-sensitive basis by default. This means that Mary and mary are not the same. Try modifying the query in the console to this one:

select * from accounts where first_name = 'mary'

And you'll see that we have 0 results. When comparing text in a database, it's important to always consider whether the casing of the values you're comparing is known and consistent. If you're not sure whether the casing in a column is consistent, you can use the upper() or lower() functions to convert the values in your column before they are compared. For example:

select * from accounts where upper(first_name) = 'MARY'

This query ensures that the first_name values in our database are (temporarily, only for this one query) converted to uppercase before comparing them to our literal string MARY. As a result, our query will find everyone named "Mary", regardless of the casing of their name in the database. Try it out in the SQL console above.

Exercise 1: A simple where clause

Write a query to find every person in the accounts table whose first name is Bruce.

Exercise 2: Accounting for case

Write a query to find every person in the accounts table whose first name is John, regardless of the casing of their name.

Multiple Criteria

Finding everyone named Mary is all well and good, but what if we want to find everyone whose full name is "Mary Brown"? Since we don't have a column that contains full names in our data, we will need to use multiple criteria in our where clause to do so, like this:

select *
from accounts
where first_name = 'Mary'
    and last_name = 'Brown'

This query uses the and operator to add multiple criteria to our query. The and operator requires that each of your specified criteria must be true for a row to appear in your results. Try it out in the SQL console above; you will only receive results where donors are named Mary Brown.

Exercise 3: The and operator

Write a query to select everyone in the database whose name is Emily Smith.

The or Operator

SQL also supports the or operator. This operator tells the database to return rows where at least one of the listed criteria are true. For example, if we wanted to find all donors whose first names were Monique, Clayton, or Amber, we can do that as follows:

select *
from accounts
where first_name = 'Monique'
    or first_name = 'Clayton'
    or first_name = 'Amber'

Exercise 4: The or operator

Write a query to select everyone in the database whose last name is Fuentes, Williams, or Lopez.

Combining Criteria

Finally, we can use parenthesis () to logically combine criteria to implement complex logic. For example, let's say we wanted to select everyone whose name is Michael Smith or Michael Lopez. We might be tempted to write this query as follows:

select
    *
from accounts
where first_name = 'Michael'
    and last_name = 'Smith'
    or last_name = 'Lopez'

Paste this query into the SQL console above and see what happens. We are getting a lot of people whose first name is not Michael! This is happening because the criteria are not properly grouped. SQL evaluates and criteria before it evaluates or criteria, so this query instructs the database to find rows where:

1. first_name is Michael and last_name is Smith

OR

2. last_name is Lopez

Every row that satisfies either condition #1 or condition #2 is included, so everyone whose last name is Lopez comes in, regardless of their first name. To correct this, we can use parenthesis:

select
    *
from accounts
where first_name = 'Michael'
    and (
        last_name = 'Smith'
        or last_name = 'Lopez'
    )

The parenthesis logically group everything inside it so that it is evaluated together. This way, we get every row where first_name is Michael and whose last name is one of Smith or Lopez. Any time your where clause contains both an and and an or, be sure you have properly grouped your criteria so that you receive the results you expect.

Exercise 5: Combining Criteria

Write a query to select everyone whose name is David Johnson or Jennifer Johnson.