Filtering Data with WHERE Clauses

1. The Declarative Paradigm: "What," Not "How"

Your experience with Python has trained you to think procedurally: you write step-by-step instructions that the computer executes in order. SQL operates on a different, powerful paradigm: it is a declarative language. You do not tell the database how to retrieve the data (e.g., "open this file, loop through each line, check if a condition is met..."). Instead, you simply declare the properties of the data you want to retrieve. The database's query optimizer is then responsible for figuring out the most efficient execution plan to satisfy your declaration.

This abstraction is one of the most important concepts in database engineering. To formalize this idea, please review the following resource from CMU's database course.

2. Theoretical Foundations: Relational Algebra

Every robust system is built on a solid theoretical foundation. For SQL, that foundation is relational algebra, a formal system for manipulating relations (tables). Understanding this will satisfy your goal of first-principles thinking.

The SQL WHERE clause is a direct implementation of the Selection operator in relational algebra, denoted by the Greek letter sigma ($\sigma$). The selection operator takes a relation as input and returns a new relation containing only the tuples (rows) that satisfy a given predicate (condition).

The following video from a Stanford database course provides an excellent introduction to this concept.

3. From Theory to Practice: The SELECT Statement

Now, let's translate this theory into PostgreSQL syntax. The fundamental query structure is:

SELECT column_list
FROM table_name
WHERE condition;

• SELECT: Specifies the columns you want to retrieve. Using * selects all columns.
• FROM: Specifies the table you are querying.
• WHERE: Applies the filter. This is the practical implementation of the $\sigma$ operator. It evaluates the condition for each row, and only rows where the condition evaluates to true are included in the result.

The official PostgreSQL documentation describes the logical order of query processing. It's important to see that the WHERE clause is applied before any grouping or final output computation.

4. Building Conditions: Operators

The power of the WHERE clause comes from the rich set of operators you can use to construct boolean conditions. Let's imagine we have a books table for our project with the following columns: id (integer), title (text), author (text), publication_year (integer), and word_count (integer).

Here are the essential operators you will use:

• Comparison Operators: =, <>, !=, >, <, >=, <=.

  • SELECT title FROM books WHERE author = 'George Orwell';
  • SELECT title FROM books WHERE publication_year < 1900;

• Logical Operators: AND, OR, NOT. These combine boolean expressions.

  • SELECT title FROM books WHERE publication_year > 1940 AND publication_year < 1950;
  • SELECT title FROM books WHERE author = 'J.R.R. Tolkien' OR author = 'C.S. Lewis';

• Pattern Matching: LIKE and ILIKE. LIKE is case-sensitive, while ILIKE (an ILIKE operator is a PostgreSQL extension) is case-insensitive. They use wildcards:

  • %: Matches any sequence of zero or more characters.
  • _: Matches any single character.
  • SELECT title FROM books WHERE title LIKE 'The%'; (Finds books starting with "The")
  • SELECT title FROM books WHERE author ILIKE '%martin%'; (Finds authors with "martin" anywhere in their name, case-insensitively)

• Set Membership: IN. This checks if a value is present in a list.

  • SELECT title FROM books WHERE author IN ('Jane Austen', 'George Eliot', 'Virginia Woolf');

• Range Checks: BETWEEN. This is an inclusive range check.

  • SELECT title FROM books WHERE publication_year BETWEEN 1940 AND 1950; (Equivalent to publication_year >= 1940 AND publication_year <= 1950)

• Null Checks: IS NULL and IS NOT NULL. Used to find rows where a column value is missing.

  • SELECT title FROM books WHERE publication_year IS NULL;

The following video from CMU provides a practical demonstration of some of these operators, particularly string matching.

5. Your Turn: Practical Queries

Now, it's time to practice. Assume you have PostgreSQL installed and have a psql terminal open. For now, you don't need to create a table. You can use a SELECT statement without a FROM clause to test expressions, which acts like a calculator.

For example, try running this:
SELECT 10 > 5;
This will return t for true.

Now, write the following queries for our hypothetical books table. You don't need to run them, but write them out and predict their results.

1. Find the titles of all books with a word count greater than 200,000.
2. Find the titles and authors of all books published in the 19th century (from 1801 to 1900).
3. Find the titles of all books whose author's last name is 'Dostoevsky'.
4. Find the titles of all books that are not written by 'Jane Austen'.
5. Find the titles of all books that start with 'Crime' and are written by 'Fyodor Dostoevsky'.

This practice solidifies the syntax and logic in your mind.

Conclusion

In this lesson, you have taken the first crucial step in mastering relational databases. You've learned that SQL is a declarative language, which requires a different way of thinking than procedural languages like Python. You connected the practical WHERE clause to its theoretical foundation in the relational algebra selection operator. Finally, you learned the syntax and usage of the core filtering operators in PostgreSQL to construct precise queries.

Key Takeaways:

• SQL is declarative: You define what data you want, not how to get it.
• The WHERE clause filters rows from a table based on a boolean condition.
• The WHERE clause is the SQL implementation of the relational algebra Selection operator ($\sigma$).
• You can combine comparison (=, >), logical (AND, OR), and pattern matching (LIKE) operators to create powerful and precise filters.

In the next lesson, we will move from retrieving data to defining its structure. You will learn how to design a normalized database schema, which involves choosing appropriate data types and defining primary and foreign keys. This will be your first major architectural task for the Narrative Intelligence Engine: designing the tables that will hold the books, chapters, and chunks of text for analysis.