# Thinking in Sets — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/t-sets

> Describe the result, not the loop.

## Declarative queries over relations

SQL is **declarative**: you describe which rows and columns you want, and the database's planner decides how to get them (which indexes, which join algorithms, which order). Tables are sets of rows with **no inherent order**: without ORDER BY, row order is not guaranteed. Think in whole sets ("all paid orders joined to their customers, grouped by city") rather than row-by-row loops, and know the **grain** of each table (one row per order, per order item, per day).

## Sets, order of evaluation, NULLs

SQL describes the result you want; understanding how a query is evaluated explains most surprises.

![Three ideas: relational thinking, logical order, three-valued logic.](assets/figures/sql-deep-dive/section-1-map.svg) — Figure 1.1 — Sets, logical order and NULLs.

## The sample schema used in this course

Six small tables: departments, employees (with a manager hierarchy), customers, orders, order_items and logins.

```sql
CREATE TABLE departments (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE employees (
  id int PRIMARY KEY, name text NOT NULL, dept_id int REFERENCES departments(id),
  manager_id int REFERENCES employees(id), salary int NOT NULL, hired date NOT NULL);
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text);
CREATE TABLE orders (
  id int PRIMARY KEY, customer_id int REFERENCES customers(id),
  ordered_on date NOT NULL, status text NOT NULL, amount numeric(10,2) NOT NULL);
CREATE TABLE order_items (order_id int REFERENCES orders(id), product text, qty int, price numeric(10,2));
CREATE TABLE logins (user_id int, day date);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'Support'),(4,'Legal');
INSERT INTO employees VALUES
 (1,'Asha',1,NULL,250000,'2019-03-01'), (2,'Ravi',1,1,180000,'2020-06-15'),
 (3,'Meera',1,1,180000,'2021-01-10'),  (4,'John',1,2,120000,'2023-05-01'),
 (5,'Sara',2,1,150000,'2020-02-01'),   (6,'Ken',2,5,90000,'2022-08-20'),
 (7,'Li',2,5,95000,'2024-01-05'),      (8,'Omar',3,1,70000,'2021-11-11'),
 (9,'Nina',NULL,1,60000,'2025-02-01');
INSERT INTO customers VALUES (1,'Asha','Pune'),(2,'Ravi','Delhi'),(3,'Meera','Pune'),(4,'John',NULL);
INSERT INTO orders VALUES
 (101,1,'2026-01-03','paid',1200),(102,1,'2026-01-20','paid',300),(103,2,'2026-01-21','refunded',800),
 (104,2,'2026-02-02','paid',450),(105,3,'2026-02-14','paid',2000),(106,1,'2026-02-28','paid',700),
 (107,3,'2026-03-01','cancelled',150);
INSERT INTO order_items VALUES
 (101,'lamp',1,1000),(101,'pen',10,20),(102,'mug',2,150),(103,'chair',1,800),
 (104,'pen',5,20),(104,'mug',1,350),(105,'desk',1,2000),(106,'lamp',1,700),(107,'pen',5,30);
INSERT INTO logins VALUES
 (1,'2026-03-01'),(1,'2026-03-02'),(1,'2026-03-03'),(1,'2026-03-05'),(1,'2026-03-06'),
 (2,'2026-03-02'),(2,'2026-03-04'),(2,'2026-03-05'),(2,'2026-03-06'),(2,'2026-03-07');
```

## Name the grain before writing joins

Saying "orders is one row per order, order_items one row per line" prevents most double-counting bugs.

**Quiz:** What order do rows come back in without ORDER BY?

- [ ] Primary key order
- [ ] Insertion order
- [x] No guaranteed order
- [ ] Alphabetical order

*Answer:* No guaranteed order. Always add ORDER BY when order matters.