What are the 3 questions every SQL query starts with?
- What do you want to see? (SELECT)
- Where does it live? (FROM / JOIN)
- What rules apply? (WHERE)
These questions guide the structure of an SQL query.
What does an INNER JOIN return?
Only rows that match in both tables (the overlap)
INNER JOIN is used to combine rows from two or more tables based on a related column.
What does a LEFT JOIN return?
All rows from the left table + matches from the right; unmatched right rows become NULL
LEFT JOIN includes all records from the left table and matched records from the right table.
What does a RIGHT JOIN return?
All rows from the right table + matches from the left
RIGHT JOIN includes all records from the right table and matched records from the left table.
What does a FULL OUTER JOIN return?
All rows from both tables, matched where possible, NULL where not
FULL OUTER JOIN returns all records when there is a match in either left or right table records.
What does a CROSS JOIN do?
Returns every combination of rows from table A × table B
CROSS JOIN produces a Cartesian product of the two tables.
What is the purpose of JOINs?
To combine tables side-by-side using matching columns (usually IDs)
JOINs are essential for relational database queries.
What does UNION do?
Stacks results vertically and removes duplicates
UNION combines the result sets of two or more SELECT statements.
What does UNION ALL do?
Stacks results vertically but keeps duplicates
UNION ALL is used when you want to include all records, including duplicates.
What does INTERSECT return?
Only rows that appear in both queries
INTERSECT is used to find common records between two SELECT statements.
What does EXCEPT return?
Rows from the first query that do not appear in the second (“A minus B”)
EXCEPT is used to find records in one query that are not present in another.
What is required for UNION / INTERSECT / EXCEPT to work?
Both queries must have the same number of columns and compatible data types
This ensures that the operations can be performed correctly.
In combined queries, which SELECT decides the column names?
The first SELECT
The column names in the result set are taken from the first query.
What does the WHERE clause do?
Filters rows based on conditions
WHERE is used to specify criteria for selecting records.
What does AND do in a WHERE clause?
All conditions must be true
AND is used to combine multiple conditions in a WHERE clause.
What does OR do in a WHERE clause?
At least one condition must be true
OR allows for flexibility in filtering records.
Why use brackets with OR?
To ensure the logic evaluates as expected
Brackets clarify the order of operations in complex conditions.
What does BETWEEN do?
Returns values within an inclusive range
BETWEEN is used to filter records based on a range of values.
What does NOT do?
Shows everything except the specified condition
NOT is used to exclude certain records from the result set.
What does a foreign key prevent?
- Inserting children without parents
- Deleting parents with children
- Invalid updates
Foreign keys enforce referential integrity between tables.
What is ON DELETE CASCADE?
Deleting a parent automatically deletes related child rows
This ensures that no orphaned records remain in the child table.
What is ON UPDATE CASCADE?
Updating a parent key automatically updates child rows
This maintains consistency across related records.
What problem does normalisation solve?
- Duplicate data
- Repeated information
- Inconsistent updates
- Messy tables
Normalisation organizes data to reduce redundancy.
Core idea of normalisation?
Each fact is stored once; other tables reference it via IDs
This approach minimizes data duplication.
