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.
Two queries. Two result sets. You need the rows that appear in both. This is set intersection, and SQL provides a dedicated operator for it: INTERSECT. It is one of the three set operators in standard SQL, alongside UNION and EXCEPT. Where UNION combines results and EXCEPT subtracts one from another, INTERSECT returns only the…
Every set operation answers a question about two collections. UNION asks “what is in either?” INTERSECT asks “what is in both?” And EXCEPT asks “what is in the first but not the second?” This last question—finding what is missing, what has changed, what exists in one place but not another—is one of the most common…
A query that contains another query inside it. This is the essential idea behind the subquery, and it is one of the most powerful tools in SQL. A subquery lets you use the result of one query as input to another, without creating temporary tables or writing procedural code. The WHERE clause is the most…
The WHERE clause is where subqueries are most common, but it is not where they are most powerful. A subquery in the SELECT list can compute a per-row value from another table. A subquery in the FROM clause can act as a temporary table, a derived table that exists only for the duration of the…
A subquery that references a column from the outer query is correlated. This single characteristic changes everything about how the subquery behaves. An uncorrelated subquery runs once and produces a result that the outer query uses. A correlated subquery runs once for every row the outer query processes, because its result depends on the specific…
A subquery can return a value, a list of values, or a set of rows. The EXISTS operator is different from all of these. It does not return a value. It returns a boolean: true if the subquery produces at least one row, false if it produces none. This distinction matters because EXISTS is not…
A subquery can return a single value, a list of values, or a set of rows. The comparison operators—=, >, <, >=, <=, <>—compare one value to one value. When the subquery returns multiple rows, a simple comparison fails. The quantifiers ANY, SOME, and ALL extend comparison operators to work with a set of values….
SQL does not have if statements inside a query. It has CASE. The CASE expression is the closest thing SQL has to conditional logic, and it is remarkably flexible. It can appear in the SELECT list to produce computed columns, in the WHERE clause to filter conditionally, in the ORDER BY clause to sort by…
NULL is not a value. It is the absence of a value. It does not equal anything—not even itself. It does not compare greater or less than anything. It propagates through arithmetic and string operations, turning everything it touches into NULL. This behavior is correct according to the SQL standard, but it is inconvenient in…
A string in SQL is a sequence of characters. In practice, it is rarely clean. It has leading and trailing whitespace, inconsistent casing, substrings that need extraction, parts that need concatenation, and lengths that need measuring. SQL provides a set of string functions for these operations, and although the exact names vary slightly between databases,…