WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition. While simple comparisons like = or > are common, the WHERE clause also supports more powerful operators for more complex filtering.

Database Preparation

Run this block to set up the Books table.

DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INTEGER,
    Genre VARCHAR(50),
    PublishedYear INTEGER
);

INSERT INTO Books (BookID, Title, AuthorID, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 101, 'Fantasy', 1937),
       (2, '1984', 102, 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 101, 'Fantasy', 1954),
       (4, 'Dune', 103, 'Sci-Fi', 1965);

SELECT * FROM Books;

Using Comparison Operators

SELECT Title, PublishedYear
FROM Books
WHERE PublishedYear > 1950;

The IN Operator

SELECT Title, Genre
FROM Books
WHERE Genre IN ('Sci-Fi', 'Dystopian');

The BETWEEN Operator

SELECT Title, PublishedYear
FROM Books
WHERE PublishedYear BETWEEN 1940 AND 1960;

The LIKE Operator and Wildcards

SELECT Title FROM Books WHERE Title LIKE '%the%';

Using Subqueries in a WHERE Clause

Query:

SELECT b1.Title, b1.Genre
FROM Books AS b1
WHERE 1 = (
    SELECT COUNT(*)
    FROM Books AS b2
    WHERE b2.Genre = b1.Genre
);

Explanation:

1. We use two table aliases, b1 and b2, to represent two different "views" of the Books table in the same query.
2. The outer query (FROM Books AS b1) iterates through each book, one by one. Let's say it's currently looking at "Dune" from row b1.
3. For that specific row (b1), the inner subquery runs. It checks the Books table (as b2) and counts how many books have the same genre as the current book from the outer query (WHERE b2.Genre = b1.Genre). For "Dune", the inner query counts how many books have the genre 'Sci-Fi'. It finds 1.
4. The WHERE clause of the outer query then checks if this count is equal to 1. For "Dune", 1 = 1 is true, so "Dune" is included in the final result.
5. When the outer query looks at "The Hobbit", the inner query counts how many books have the genre 'Fantasy'. It finds 2. The WHERE 1 = 2 condition is false, so "The Hobbit" is not included.

Result:

Hands-on Exercise

Task: Find the titles of all books published between 1940 and 1960.

Your Query:

-- Write your query here

Expected Results: