Queries - WitDatabase

sql

SELECT [DISTINCT | ALL] [TOP n] select_list
FROM      table_source [, ...]
WHERE     condition
GROUP BY  expression [, ...]
HAVING    condition
ORDER BY  expression [ASC | DESC] [NULLS FIRST | LAST] [, ...]
LIMIT     count [OFFSET offset]

Limiting rows

Three spellings, because three databases spell it differently and a query written for any of them should run:

sql

SELECT * FROM Orders ORDER BY CreatedAt DESC LIMIT 10;
SELECT * FROM Orders ORDER BY CreatedAt DESC LIMIT 10 OFFSET 20;
SELECT * FROM Orders ORDER BY CreatedAt DESC LIMIT 20, 10;   -- MySQL: offset, then count
SELECT TOP 10 * FROM Orders ORDER BY CreatedAt DESC;         -- SQL Server
SELECT * FROM Orders ORDER BY CreatedAt DESC OFFSET 20;      -- skip without taking

TOP maps onto the same limit internally, so it is a spelling rather than a second concept.

Pair a limit with an ORDER BY. Without one, which ten rows you get is whatever the plan happened to produce.

Joins

sql

SELECT o.Id, c.Name
FROM Orders AS o
INNER JOIN Customers AS c ON o.CustomerId = c.Id;

INNER, LEFT [OUTER], RIGHT [OUTER], FULL [OUTER] and CROSS are all available.

Joins are this engine's weakest area, and the cost grows with each table added, so a four-table join is where you will notice it.

An INNER or LEFT join on an equality condition can be answered by a hash join, and the planner decides from the estimated row counts whether building the table is worth it. Everything else runs as a nested loop, including RIGHT, FULL and CROSS, and anything joined on a range or an expression. EXPLAIN says which you got.

Index the columns you join on, and consider whether two smaller queries beat one large join here. See limitations.

Subqueries

In FROM, as a derived table with a required alias, and an optional column list to rename what it returns:

sql

SELECT c.Name, t.Total
FROM Customers AS c
JOIN (SELECT CustomerId, SUM(Total) AS Total
      FROM Orders GROUP BY CustomerId) AS t
    ON t.CustomerId = c.Id;

SELECT * FROM (SELECT Id, Name FROM Users) AS u (Key, Label);

The AS is required on a derived table and optional on a LATERAL or an APPLY.

In WHERE, as a value, a set, or an existence test:

sql

SELECT * FROM Orders WHERE Total > (SELECT AVG(Total) FROM Orders);
SELECT * FROM Users WHERE Id IN (SELECT UserId FROM Orders);
SELECT * FROM Users AS u WHERE EXISTS (SELECT 1 FROM Orders WHERE UserId = u.Id);
SELECT * FROM Products WHERE Price > ALL (SELECT Price FROM Discontinued);

ANY, SOME and ALL all work as quantifiers.

LATERAL and APPLY

A subquery in FROM that may refer to the rows beside it:

sql

SELECT c.Name, recent.Id, recent.CreatedAt
FROM Customers AS c
CROSS APPLY (SELECT Id, CreatedAt FROM Orders
             WHERE CustomerId = c.Id
             ORDER BY CreatedAt DESC LIMIT 3) AS recent;

CROSS APPLY drops customers with no orders, OUTER APPLY keeps them with nulls. LATERAL is the standard spelling of the same thing:

sql

FROM Customers AS c,
     LATERAL (SELECT ... WHERE CustomerId = c.Id) AS recent

This is the shape to reach for when you want the top few rows per group, which a plain join cannot express.

It is also the expensive shape: the subquery is planned once per outer row, because its plan can depend on the outer values, and that is the whole point of the construct. The planner will not reach for it on its own.

Note that EF Core cannot translate to it yet. A correlated Take refuses at translation time rather than emitting SQL, so this is a construct to write by hand.

FROM T, LATERAL (...) takes its left from the FROM list, and a LATERAL written with nothing before it is refused: it has nothing to correlate with.

Common table expressions

sql

WITH ActiveUsers AS (
    SELECT Id, Name FROM Users WHERE IsActive = TRUE
),
RecentOrders AS (
    SELECT UserId, COUNT(*) AS Orders
    FROM Orders WHERE CreatedAt > @since GROUP BY UserId
)
SELECT u.Name, COALESCE(o.Orders, 0) AS Orders
FROM ActiveUsers AS u
LEFT JOIN RecentOrders AS o ON o.UserId = u.Id;

WITH RECURSIVE walks a hierarchy:

sql

WITH RECURSIVE Tree AS (
    SELECT Id, ParentId, Name, 0 AS Depth
    FROM Categories WHERE ParentId IS NULL

    UNION ALL

    SELECT c.Id, c.ParentId, c.Name, t.Depth + 1
    FROM Categories AS c
    JOIN Tree AS t ON c.ParentId = t.Id
)
SELECT * FROM Tree ORDER BY Depth, Name;

The first arm seeds it, the second extends it, and the whole thing stops when the second returns nothing. Give a recursive query a depth limit unless you are certain the data is acyclic.

Set operations

sql

SELECT Email FROM Users
UNION
SELECT Email FROM Subscribers;

UNION removes duplicates, UNION ALL keeps them and is cheaper. INTERSECT and EXCEPT are there too. A trailing ORDER BY or LIMIT applies to the combined result rather than to the last arm.

VALUES can stand where a query does, which is handy for a small fixed list:

sql

SELECT * FROM Orders
WHERE Status IN (SELECT * FROM (VALUES ('pending'), ('paid')) AS s(Status));

Grouping

sql

SELECT CustomerId, COUNT(*) AS Orders, SUM(Total) AS Revenue
FROM Orders
GROUP BY CustomerId
HAVING SUM(Total) > 1000
ORDER BY Revenue DESC;

WHERE filters rows before grouping, HAVING filters groups after.

A grouped query builds each row out of its select list, so a column you group by has to appear there if ORDER BY or HAVING is going to reach it. See limitations.

Window functions

An aggregate that keeps the rows rather than collapsing them:

sql

SELECT
    Name,
    Department,
    Salary,
    RANK()       OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank,
    AVG(Salary)  OVER (PARTITION BY Department)                      AS DeptAverage,
    SUM(Salary)  OVER (ORDER BY HiredAt)                             AS RunningTotal
FROM Employees;

Available: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, PERCENT_RANK, CUME_DIST, and the ordinary aggregates with OVER.

The frame decides which rows each result is computed over, and the default depends on whether the window is ordered. With an ORDER BY, the frame runs from the start of the partition to the current row, which is what makes SUM(x) OVER (ORDER BY y) a running total. Without one, it is the whole partition, which is what makes AVG(x) OVER (PARTITION BY d) the department average.

Say it explicitly when you want something else:

sql

AVG(Total) OVER (ORDER BY CreatedAt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

ROWS counts rows, RANGE counts values. Bounds are UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING.

Locking rows you read

sql

SELECT * FROM Jobs WHERE Status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

FOR UPDATE and FOR SHARE hold a lock until the transaction ends. NOWAIT fails rather than waiting; SKIP LOCKED passes over locked rows, which is how several workers pull from one queue without queueing behind each other. See transactions.

Two operators worth knowing

GLOB matches with shell-style wildcards, * and ?, and is case-sensitive where LIKE is not:

sql

SELECT * FROM Files WHERE Name GLOB '*.log';

COLLATE names a collation for a comparison or a sort. Four are named in the grammar: BINARY, NOCASE, UNICODE_CI and UNICODE_COLLATE.

sql

SELECT * FROM Users ORDER BY Name COLLATE NOCASE;
SELECT * FROM Users WHERE Name = 'ana' COLLATE UNICODE_CI;

Seeing the plan

sql

EXPLAIN SELECT * FROM Orders WHERE CustomerId = 123;
EXPLAIN QUERY PLAN SELECT ...;

Run it whenever a query is slower than you expected: it says whether an index was used and which. Studio has it on a panel of its own.

Where to go next

Functions, what goes in a select list
DML, changing rows rather than reading them
Indexing, making these queries fast
Transactions, FOR UPDATE and isolation