Structured Query Language, the standard language for interacting with relational databases. It covers querying data with SELECT, modifying it with INSERT, UPDATE, and DELETE, defining schemas with CREATE and ALTER, and controlling access with GRANT and REVOKE. SQL is the universal interface to databases like PostgreSQL, MySQL, SQLite, and SQL Server.
The HAVING clause filters groups after aggregation. It is the counterpart to WHERE, which filters rows before grouping. A query that computes a total per customer, an average per department, or a count per day uses GROUP BY to create the groups, and HAVING to keep only the groups that meet a condition. Without HAVING,…
SQL queries are written in one order and executed in another. The written order is familiar: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. The execution order is different, and understanding it explains why column aliases work in ORDER BY but not in WHERE, why aggregate functions cannot appear in WHERE, why HAVING can reference…
A relational database stores data in separate tables to avoid redundancy. Customers live in one table, orders in another, products in a third. Each table holds only the data relevant to its entity, and relationships between entities are represented by shared keys: an order row contains a customer_id that points to a customer row. A…
The INNER JOIN is the most common join type and the one you reach for by default when you need to combine data from two tables. It returns only the rows where the join condition matches in both tables. Rows with no match in either table are excluded from the result. This behavior is what…
The LEFT JOIN returns all rows from the left table and the matching rows from the right table. When a left row has no match in the right table, the right table’s columns are filled with NULL, and the row still appears in the result. This is the behavior that distinguishes an outer join from…
The RIGHT JOIN is the mirror image of the LEFT JOIN. It returns all rows from the right table and the matching rows from the left table. When a right row has no match in the left table, the left table’s columns are filled with NULL, and the row still appears in the result. The…
The FULL OUTER JOIN returns all rows from both tables, matching rows where the join condition is satisfied and filling the unmatched side with NULLs where it is not. It is the union of a LEFT JOIN and a RIGHT JOIN: every left row appears, every right row appears, and rows that match appear once…
The CROSS JOIN produces the Cartesian product of two tables: every row from the left table paired with every row from the right table. It has no join condition, and it makes no attempt to match rows by any criterion. If the left table has 100 rows and the right table has 50 rows, the…
A self join is a join in which a table is joined to itself. The table appears twice in the FROM clause, and the two instances are given different aliases so the query can distinguish them. The join condition relates a row in one instance to a row in the other, which is how the…
The UNION operator combines the result sets of two or more SELECT statements into a single result set. Unlike a join, which combines columns from two tables side by side, a union combines rows from two queries one after another. The two queries must have the same number of columns, the corresponding columns must have…