Learn You Some SQL For Great Good - Unlearn What You Have Learned OR How Select Logically Works (the WHERE clause)

"Yet marked I where the bolt of Cupid fell:

It fell upon a little western flower,
Before, milk-white; now purple with love's wound:
And maidens call it ‘ love in idleness.’
Fetch me that flower – the herb I showed thee once"
-- Shakespeare, A Midsummer Night's Dream
Act II, Scene I, Lines 165-169

WHERE is my data?

In our journey through the SELECT clause we have finally moved on to the WHERE clause.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

The simplest way to think of the WHERE clause is think of it as a filter.

Filtering

Given our data set of playwrights:

INSERT INTO playwrights
(name, birthYear, birthEra)
VALUES
  ('Oscar Wilde', 1854, 'AD')
 ,('Euripides', 480, 'BC')
 ,('Aeschylus', 525, 'BC')
 ,('Sophocles', 497, 'BC')
 ,('William Shakespeare', 1564, 'AD')
 ,('Jacopone da Todi', 1230, 'AD')
;

If we wanted to only get the playwrights born in BC we would apply a filter using the WHERE clause which states only give me records where the birthEra has the value of BC.



SELECT *
  FROM playwrights
  WHERE birthera = 'BC'
;

(on SQL Fiddle)

In our WHERE clause we state that the value must equal BC, yielding the records of Euripides, Aeschylus, and Sophocles.

Likewise we can create multi-conditional statements to apply even more filtering rules against our data set.  Say we want to limit our records to just the playwrights born after the founding of America.

SELECT *
  FROM playwrights
  WHERE birthera = 'AD'
    AND birthyear > 1775
;

(on SQL Fiddle)

We can use a logical AND to state that the birthEra must be AD and the birthYear must be after 1775.  We can of course also write the SQL statement using a greater than or equal to 1776.

SELECT *
  FROM playwrights
  WHERE birthera = 'AD'
    AND birthyear >= 1776
;

Both will yield the same resulting record set.