SQL (Structured Query Language)

This covers the core principles of SQL, the standard language for communicating with databases.

1. Logical Order of Execution

The order in which you write SQL (SELECT -> FROM…) differs from the order in which the database actually executes it. Not knowing this can cause errors.

1. FROM / JOIN: First, find the tables to retrieve data from.
2. WHERE: Filter out rows that don’t meet the conditions beforehand. (Performance key)
3. GROUP BY: Group the remaining data.
4. HAVING: Keep only groups that satisfy the conditions.
5. SELECT: Select and calculate the columns to output. (Window Functions happen here too!)
6. ORDER BY: Sort the results.
7. LIMIT: Limit the number of results.

Why?: This is why you can’t use an alias created in SELECT in the WHERE clause. WHERE is executed before SELECT!

2. Set Theory

JOIN is based on the concept of sets.

• INNER JOIN: Intersection ($A \cap B$) - Data that exists in both tables.
• LEFT JOIN: All of $A$ + ($A \cap B$) - Keep all from the left, attach from the right if exists.
• FULL OUTER JOIN: Union ($A \cup B$) - Keep everything from both sides.
• CROSS JOIN: Cartesian product ($A \times B$) - All possible combinations (multiply row counts).

Curriculum