Joins, grouping, indexes, transactions and query tuning
Practise these as flashcards. One or two lines each; the ones you miss come back first.
Start freeLIMIT restricts the number of rows returned by a query. For example, LIMIT 10 will only return the first 10 rows from the result set.
You use IS NULL or IS NOT NULL to check for NULL values. Comparisons like '=' or '!=' don't work with NULL because NULL means unknown.
INNER JOIN returns only rows with matching keys in both tables, while LEFT JOIN returns all rows from the left table and fills in NULLs for missing matches on the right.
GROUP BY is used to aggregate data by grouping rows based on one or more columns, allowing you to apply aggregate functions like COUNT or SUM to each group.
A common mistake is forgetting to specify the join condition, which results in a Cartesian product and can return millions of rows unexpectedly. Another mistake is misunderstanding how NULLs behave in outer joins.
HAVING is used to filter groups after aggregation, while WHERE filters rows before grouping. For example, to filter departments with more than 10 employees, you use HAVING COUNT(*) > 10.
A covering index includes all columns needed for a query, so the database can satisfy the query using only the index without accessing the table. This improves performance, especially for read-heavy workloads.
The order matters because the index can only efficiently filter or sort based on the leading columns. Queries must match the leftmost columns to benefit from the index.
An execution plan shows how the database will execute a query, including which indexes are used, join methods, and whether any full table scans occur. It helps diagnose performance issues.
A subquery is a nested query inside another query, while a CTE (Common Table Expression) uses WITH to define a temporary result set. CTEs make complex queries more readable and maintainable.
You can use a subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or use window functions for more advanced cases.
Window functions perform calculations across a set of rows related to the current row, like ranking or running totals, without collapsing groups. Aggregate functions summarize groups into single values.