SQLite - AND & OR 條件語句

Hello, aspiring database enthusiasts! Today, we're going to dive into the world of SQLite and explore two powerful operators that will help you create more complex and precise database queries: the AND and OR clauses. As your friendly neighborhood computer teacher, I'm excited to guide you through this journey. So, grab your virtual notepads, and let's get started!

The AND Operator

Imagine you're organizing a massive library of books. You want to find all the books written by J.K. Rowling AND published after the year 2000. That's where the AND operator comes in handy!

What is the AND Operator?

The AND operator allows you to combine multiple conditions in your SQL query. For a row to be included in the result set, ALL conditions joined by AND must be true.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Examples

Let's create a simple "books" table to work with:

CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT,
author TEXT,
publication_year INTEGER,
genre TEXT
);

INSERT INTO books (title, author, publication_year, genre)
VALUES
('Harry Potter and the Goblet of Fire', 'J.K. Rowling', 2000, 'Fantasy'),
('The Da Vinci Code', 'Dan Brown', 2003, 'Thriller'),
('To Kill a Mockingbird', 'Harper Lee', 1960, 'Classic'),
('1984', 'George Orwell', 1949, 'Dystopian'),
('The Hunger Games', 'Suzanne Collins', 2008, 'Young Adult');

Now, let's use the AND operator to find all fantasy books published after 2000:

SELECT title, author, publication_year
FROM books
WHERE genre = 'Fantasy' AND publication_year > 2000;

This query will return:

title                               | author      | publication_year
------------------------------------------------------------
Harry Potter and the Goblet of Fire | J.K. Rowling | 2000

Explanation: This query selects only the books that satisfy both conditions: the genre is 'Fantasy' AND the publication year is greater than 2000.

Let's try another example. We'll find all books written by J.K. Rowling and published in 2000:

SELECT title, author, publication_year
FROM books
WHERE author = 'J.K. Rowling' AND publication_year = 2000;

This query will return the same result as before because it's the only book in our table that meets both conditions.

Pro Tip

You can use as many AND operators as you need in a single query. For example:

SELECT title, author, publication_year, genre
FROM books
WHERE author = 'J.K. Rowling' AND publication_year = 2000 AND genre = 'Fantasy';

This query adds one more condition to our previous example.

The OR Operator

Now, let's say you're in the mood for either a fantasy book OR a thriller. That's where the OR operator comes to the rescue!

What is the OR Operator?

The OR operator allows you to combine multiple conditions in your SQL query. For a row to be included in the result set, at least ONE of the conditions joined by OR must be true.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Examples

Using our "books" table from before, let's find all books that are either in the Fantasy genre OR published after 2005:

SELECT title, author, publication_year, genre
FROM books
WHERE genre = 'Fantasy' OR publication_year > 2005;

This query will return:

title                               | author           | publication_year | genre
----------------------------------------------------------------------------------
Harry Potter and the Goblet of Fire | J.K. Rowling     | 2000             | Fantasy
The Hunger Games                    | Suzanne Collins | 2008             | Young Adult

Explanation: This query selects books that satisfy either of the conditions: the genre is 'Fantasy' OR the publication year is greater than 2005.

Let's try another example. We'll find all books written by either J.K. Rowling or Dan Brown:

SELECT title, author, publication_year, genre
FROM books
WHERE author = 'J.K. Rowling' OR author = 'Dan Brown';

This query will return:

title                               | author       | publication_year | genre
----------------------------------------------------------------------------------
Harry Potter and the Goblet of Fire | J.K. Rowling | 2000             | Fantasy
The Da Vinci Code                   | Dan Brown    | 2003             | Thriller

Combining AND and OR

Here's where things get really interesting! You can combine AND and OR operators in the same query. But be careful – the order matters!

Let's find all Fantasy books OR books published after 2005 that are not in the Young Adult genre:

SELECT title, author, publication_year, genre
FROM books
WHERE (genre = 'Fantasy' OR publication_year > 2005) AND genre != 'Young Adult';

This query will return:

title                               | author       | publication_year | genre
----------------------------------------------------------------------------------
Harry Potter and the Goblet of Fire | J.K. Rowling | 2000             | Fantasy

Explanation: The parentheses are crucial here. They ensure that the OR condition is evaluated first, and then the result is combined with the AND condition.

Pro Tip

When combining AND and OR, always use parentheses to make your intentions clear. It helps both you and SQLite understand the logic of your query.

Summary

Here's a quick reference table of the AND and OR operators:

Operator	Description	Example
AND	Returns true if all conditions are true	WHERE condition1 AND condition2
OR	Returns true if at least one condition is true	WHERE condition1 OR condition2

Remember, the AND operator is like a strict librarian who only gives you a book if you meet ALL the criteria. The OR operator is more like a lenient friend who's happy if you meet ANY of the conditions.

Practice using these operators, and soon you'll be crafting complex queries like a pro! Happy querying, future database wizards!

Credits: Image by storyset