WHERE

Picking rows with WHERE

SQLite in your browser
Lesson concept What you'll do
1 / 5

Keep only the books of one genre.

WHERE keeps the rows that pass a test, like a filter in Excel. Text goes in single quotes.

The database
11 rows
book_idtitleauthorgenreyearpages
1Angels & DemonsDan BrownThriller2000616
2The Fellowship of the RingJ.R.R. TolkienFantasy1954423
3War and PeaceLeo TolstoyFiction18691225
4Animal FarmGeorge OrwellFiction1945112
5The ShiningStephen KingHorror1977447
6Gone GirlGillian FlynnThriller2012419
7The Sign of the FourArthur Conan DoyleMystery1890122
8Harry Potter and the Philosopher's StoneJ.K. RowlingFantasy1997223
9Sense and SensibilityJane AustenRomance1811400
10The Hound of the BaskervillesArthur Conan DoyleMystery1902256
11The Great GatsbyF. Scott FitzgeraldFiction1925180
Click a name to type it
Your query
Hint: click the green edit button on the board, or type it yourself
Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How WHERE works

Written order vs the order SQL works in

YOU WRITE

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY
  7. LIMIT

SQL WORKS THROUGH IT

  1. 1. FROM take the table
  2. 2. WHERE keep some rows
  3. 3. GROUP BY make piles
  4. 4. HAVING keep some piles
  5. 5. SELECT pick and work out columns
  6. 6. ORDER BY sort
  7. 7. LIMIT keep the first few

WHERE picks the rows before SELECT picks the columns.

  • What does WHERE do?

    WHERE keeps only the rows where a condition is true. Every other row is left out of the result (the table itself doesn't change).

  • Where does WHERE go?

    After FROM: SELECT title, genre FROM books WHERE genre = 'Horror';

  • Why single quotes?

    Text values go in single quotes: 'Horror'. Without quotes, SQL thinks Horror is the name of a column. Numbers don't need quotes: WHERE year = 1997.

  • Do capitals matter in the text?

    In SQLite, yes: 'horror' doesn't match 'Horror'. Spell the value as it's stored. Some databases ignore capitals; the table below shows which.

  • How is this like Excel?

    It's the same idea as a filter on one column, or the FILTER function: =FILTER(B2:D12, D2:D12="Horror").

Other databases

How other databases do it

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Quotes around text
DatabaseHowNote
SQLite (these lessons)genre = 'Horror'Double quotes mean a column name, but when there is no such column SQLite quietly reads "Horror" as text. Don't rely on it.
PostgreSQLgenre = 'Horror'Double quotes are always a column name, so "Horror" is an error.
MySQL / MariaDBgenre = 'Horror'Double quotes also work for text, unless the ANSI_QUOTES mode is on.
SQL Servergenre = 'Horror'Double quotes are a column name (QUOTED_IDENTIFIER is on by default).
Oraclegenre = 'Horror'Double quotes are always a column name.
Sybase ASEgenre = 'Horror'Double quotes also work for text, because quoted_identifier is off by default.
Does 'horror' find Horror?
DatabaseHowNote
SQLite (these lessons)No= cares about capitals. LIKE ignores them (for A to Z).
PostgreSQLNo= cares about capitals. Use ILIKE, or LOWER(genre) = 'horror'.
MySQL / MariaDBYesThe usual collations ignore capitals.
SQL ServerYesThe default collation ignores capitals (a case-sensitive one can be chosen).
OracleNo= cares about capitals. Use UPPER(genre) = 'HORROR'.
Sybase ASENoThe default binary sort order cares about capitals.

Know someone fighting with a spreadsheet? Send them this page.