No history yet

Relational Logic Review

Thinking in Sets

You're used to telling Python exactly what to do, step by step. Loop through this list, check that condition, append to this new list. This is an imperative approach. You define the how.

SQL is different. It's declarative. You don't tell the database how to get the data; you describe the data you want. You define the what. Think of it less like writing a recipe and more like ordering from a menu. You describe the final dish, and the chef (the database engine) figures out the most efficient way to make it.

This approach is built on relational algebra and set theory. Every query operates on one or more tables (sets of data) and produces a new table (a new set of data) as its result. This shift from step-by-step instructions to describing a desired outcome is the most important mental leap when moving from Python to SQL.

The Query's Secret Order

While you write a SQL query in a specific sequence (SELECT, FROM, WHERE...), the database engine doesn't execute it in that order. Understanding the logical execution order is crucial for writing complex queries correctly, especially when you get to window functions and aggregations.

Think of it as building a car. You don't start with the paint job. You start with the chassis and engine, then add wheels, and so on. The final appearance comes later.

Here’s the logical order of operations for a standard query:

  1. FROM / JOIN: First, the database determines the total working set of data. It brings all the tables specified in FROM and performs any JOINs to link them together.
  2. WHERE: It then filters this massive set of rows based on the conditions you provide. Only rows that meet the criteria pass through.
  3. GROUP BY: If you're aggregating, the remaining rows are now grouped together based on common values in specified columns.
  4. HAVING: After grouping, the HAVING clause filters the groups themselves. It's like a WHERE clause, but for groups, not individual rows.
  5. SELECT: The engine finally determines which columns to include in the output. This is also where expressions and column aliases are calculated.
  6. ORDER BY: Lastly, the resulting rows are sorted according to your instructions.

Knowing this order explains why you can't use a SELECT alias in a WHERE clause. The WHERE clause is processed long before the alias is even created.

The WHERE clause is where you apply to your data. Each condition in the WHERE clause is a predicate, an expression that evaluates to either TRUE or FALSE for each row. When you combine conditions with AND and OR, you're building a more complex logical statement that defines the exact subset of data you're interested in.

-- Select employees in the Sales department hired after 2022
-- OR any employee in the Engineering department.
SELECT
    employee_name,
    department,
    hire_date
FROM
    employees
WHERE
    (department = 'Sales' AND hire_date > '2022-12-31')
    OR
    (department = 'Engineering');

The Deal with NULL

In Python, you have None. In SQL, you have NULL. They both represent a missing or unknown value, but SQL treats NULL with a special kind of logic. Because NULL means "unknown," it can't be equal to or unequal to anything, including another NULL. The result of NULL = NULL isn't TRUE or FALSE, it's UNKNOWN.

This leads to (or ternary logic). Any logical comparison involving a NULL will typically result in UNKNOWN. This is a common tripwire for people new to SQL. A WHERE clause only returns rows for which the condition is TRUE. It discards rows that evaluate to FALSE and rows that evaluate to UNKNOWN.

If you ask a database WHERE department = 'Sales' and some rows have a NULL department, those rows won't be returned. But they also won't be returned if you ask WHERE department != 'Sales'. They are simply excluded from both results.

To handle this, SQL provides the IS NULL and IS NOT NULL operators. These are the correct ways to specifically check for the presence or absence of a value.

ABA AND BA OR B
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
TRUEUNKNOWNUNKNOWNTRUE
FALSEFALSEFALSEFALSE
FALSEUNKNOWNFALSEUNKNOWN
UNKNOWNUNKNOWNUNKNOWNUNKNOWN
-- Correctly select customers who have not been assigned a region
SELECT
    customer_name,
    region
FROM
    customers
WHERE
    region IS NULL;

Mastering this logical foundation, from execution order to the nuances of NULL, is what separates basic query writing from robust, predictable data engineering. It ensures that the sets of data you produce are exactly the sets you intended to create.

Time to check your understanding of these core logical concepts.

Quiz Questions 1/5

How does the programming paradigm of SQL differ from an imperative language like Python?

Quiz Questions 2/5

In the logical execution order of a SQL query, which clause is processed immediately after the rows are grouped by the GROUP BY clause?

With this review of relational logic complete, you're better equipped to write precise and efficient SQL.