SQL
Correlated Subqueries, EXISTS and NOT EXISTS
Subqueries that reference the outer row and run once per row. Learn EXISTS and NOT EXISTS, why they are NULL safe, and when correlation costs too much...
SQL
A complete SQL path: fundamentals, DDL and DML, querying, joins, subqueries, CTEs, window functions, design, indexing, transactions, performance and security.
91 notes in 31 topics
Topics in SQL
SQL Basics 6
DDL 3
DML 3
SELECT 3
WHERE 3
Functions 4
NULL and Logic 2
Aggregate Functions 2
GROUP BY 3
JOIN 5
Subqueries 4
Set Operations 2
CTEs 2
Constraints 3
Keys 2
Relationships 2
Database Design 2
Normalisation 3
Views 2
Window Functions 4
Advanced SQL 6
Procedures and Functions 2
Triggers 2
Temporary Tables 2
Transactions 2
Isolation and Locking 3
Indexes 3
Query Performance 3
SQL Security 2
SQL Dialects 2
Practical SQL 4
Notes
SQL
Subqueries in SELECT, FROM and WHERE: Join vs Subquery vs EXISTS
The same question answered four ways, with a straight comparison of readability, NULL safety and cost - and a rule for choosing between them.
SQL
UNION and UNION ALL
Stacking two result sets on top of each other. Learn the column compatibility rules, why UNION ALL is faster, and where ORDER BY belongs.
SQL
INTERSECT, EXCEPT and Column Compatibility
The two set operations MySQL waited longest to add: rows in both sets, and rows in one set but not the other - plus how to emulate them where they are...
SQL
WITH: Single and Multiple CTEs
A CTE names a query so the rest of the statement can read it like a table. Learn single and chained CTEs, and when a CTE beats a subquery or a view.
SQL
Recursive CTEs
Query a hierarchy of unknown depth, generate a series of rows, or follow a chain of references - all with one self referencing CTE.
SQL
NOT NULL, DEFAULT, UNIQUE and CHECK
The four constraints that guard a single table. What each one enforces, how NULL interacts with UNIQUE and CHECK, and why the database is the right pl...
SQL
PRIMARY KEY and FOREIGN KEY
The two constraints that give a database its structure: one that identifies a row, and one that guarantees a reference points at something real.
SQL
Referential Integrity, Cascades and Constraint Management
What happens to child rows when a parent is deleted or updated, how to choose between RESTRICT, CASCADE and SET NULL, and how to add, drop and disable...
SQL
Candidate, Primary, Alternate and Composite Keys
The vocabulary of keys, explained on one table: which columns could identify a row, which one you choose, and what happens to the rest.
SQL
Natural vs Surrogate Keys
Should the primary key come from the data or be generated? The trade offs, the failure modes of each, and the pattern most teams settle on.
SQL
One to One and One to Many Relationships
The two simplest relationships and the single rule that decides where the foreign key goes - plus when a one to one split is actually worth it.