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.
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE or WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
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.
