AND

AND, OR and IN

Module 2 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Pick rows that pass more than one test.

AND needs both tests, OR either one, and IN matches any value in a list.

Next: Either genre

On your own New data, same idea

Show the title and genre of the Mystery and Thriller books.

The database

11 rows

book_id	title	author	genre	year	pages
1	Mockingjay	Suzanne Collins	Science Fiction	2010	390
2	The Shining	Stephen King	Horror	1977	447
3	The Catcher in the Rye	J.D. Salinger	Fiction	1951	234
4	Harry Potter and the Philosopher's Stone	J.K. Rowling	Fantasy	1997	223
5	Little Women	Louisa May Alcott	Fiction	1868	449
6	War and Peace	Leo Tolstoy	Fiction	1869	1225
7	The Girl with the Dragon Tattoo	Stieg Larsson	Thriller	2005	465
8	Sense and Sensibility	Jane Austen	Romance	1811	400
9	The Fellowship of the Ring	J.R.R. Tolkien	Fantasy	1954	423
10	A Study in Scarlet	Arthur Conan Doyle	Mystery	1887	108
11	The Da Vinci Code	Dan Brown	Thriller	2003	454

Your query Ctrl + Enter runs it

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 AND, OR, IN works

When do I use AND?

When every test must be true: genre = 'Science Fiction' AND year < 1960 keeps only old Science Fiction.
When do I use OR?

When any one test is enough: genre = 'Fantasy' OR genre = 'Horror' keeps books of either genre. A book can't be Fantasy AND Horror here, so AND would find nothing.
What does IN do?

WHERE genre IN ('Fantasy', 'Horror')
It's a short way to write several ORs on one column. Put the values in brackets, separated by commas.
What if I mix AND and OR?

AND is worked out before OR, like * before +. Use brackets to be sure: (genre = 'Fantasy' OR genre = 'Horror') AND pages < 300.
How is this like Excel?

The same as AND and OR inside IF: =IF(AND(D2="Science Fiction", E2<1960), ...), or COUNTIFS with two conditions.

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