Concepts SQL
Explore, learn and master the essentials, at your own pace.
ABS – absolute value
ABS returns the absolute value of a number (it drops the sign).
no SELECT *
Avoiding SELECT * makes things clearer. You know exactly what you are returning. Less exposure when the schema changes.
explicit aliases
Explicit aliases make the columns clear. Prefer order_total over t. It avoids name collisions.
BETWEEN / NOT BETWEEN
BETWEEN checks that a value falls inside a range (both bounds included). Works with numbers and dates.
primary and foreign keys
A primary key identifies a row uniquely. A foreign key points at another table. orders.customer_id to customers.id, products...
columns listed explicitly
Listing the columns makes a query stable. It keeps useless columns out. It makes review easier.
CONCAT and putting strings together
CONCAT joins several strings. In PostgreSQL you can also use the || operator.
CREATE TABLE
CREATE TABLE creates a table. You set the columns, types and constraints. It runs once.
DATE_PART – pulling one component out of a date
DATE_PART pulls one component out of a date (year, month, day, hour and so on). An alternative syntax to EXTRACT.
DISTINCT
DISTINCT removes duplicates. It applies to the selected columns. Careful: it can cost more in performance.
DROP TABLE
DROP TABLE removes the table. A destructive action. Best avoided in production.
Rounding functions: ROUND, CEIL, FLOOR
ROUND rounds a number. CEIL rounds up. FLOOR rounds down.
FOREIGN KEY
A FOREIGN KEY links two tables. It keeps the data consistent. It blocks invalid references.
standardised SQL formatting
Standardised SQL is easier to read back. The same style everywhere. Less ambiguity.
FROM
FROM names the source table. A query reads its data from that table first. You can then filter with WHERE.
handling NULL
A NULL means "unknown or missing value". = NULL does not work. Use IS NULL or IS NOT NULL.
handling NULL explicitly
Handling NULLs keeps surprises out of your results. Use COALESCE where needed. Use IS NULL to filter.
granularity under control
Granularity = the level of detail of one row. An order is not an order line. A JOIN can change the granularity.
GREATEST and LEAST
GREATEST returns the largest value out of a list of arguments. LEAST returns the smallest. They are scalar functions, unlike the MAX/MIN aggregates.
IN / NOT IN
IN tests whether a value is in a list. NOT IN excludes it. Careful with NOT IN and NULL.
consistent indentation
Consistent indentation makes the blocks obvious. Especially useful with JOINs and CTEs. It cuts down misreadings.
LENGTH
LENGTH returns the number of characters in a string.
LIKE and ILIKE
LIKE filters by pattern with % (anything) and _ (a single character). ILIKE is the case-insensitive variant.
LIMIT
LIMIT caps the number of rows returned. Handy for trying a query out. Often paired with ORDER BY.
LTRIM, RTRIM, BTRIM
Removes spaces or characters from the start and/or end of a string.
MAKE_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
MAKE_DATE builds a date from its components. CURRENT_TIME returns the current time. CURRENT_TIMESTAMP returns the current date and time with time zone.
MOD, POWER, SQRT, TRUNC
MOD returns the remainder of a division, POWER raises to a power, SQRT takes the square root, and TRUNC cuts a number off without rounding.
SQL keywords in upper case
Putting keywords in upper case makes a query easier to read. SELECT, FROM, WHERE. Identifiers stay in lower case.
descriptive CTE names
A CTE needs a name that describes what it does. For instance: order_totals, active_customers.
logical and comparison operators
Operators compare and combine conditions. Comparison: =, <, >=. Logical: AND, OR, NOT
ORDER BY
ORDER BY sorts the result. ASC = ascending. DESC = descending.
PRIMARY KEY
A PRIMARY KEY identifies a row. Unique. Never NULL.
SELECT
SELECT picks the columns to display. You can show one column or several. Avoid SELECT * where you can.
TRIM
TRIM removes the spaces at the start and end of a string.
data types
The type sets the stored format. VARCHAR: text. INT: whole number. DECIMAL: amount. TIMESTAMP: date and time
one intent per CTE
One CTE = one intent. One clear step. Not a catch-all.
UPPER and LOWER
UPPER puts a string in upper case, LOWER in lower case — to normalise it before comparing.
WHERE
WHERE filters rows. Without WHERE you get everything. With WHERE you narrow it down to a condition.
ALTER TABLE
ALTER TABLE changes the structure. Add a column. Add a constraint.
ANY, ALL with arrays
ANY tests whether a value matches at least one element of an array (like IN). ALL tests whether it matches every one. @>, <@ and && are the containment operators.
Date arithmetic and intervals
You add a duration to a date with INTERVAL. Subtracting two dates gives a number of days. AGE() works out the gap between two dates.
ARRAY_AGG
ARRAY_AGG aggregates the values of a group into a PostgreSQL array. Handy for gathering ids or values without flattening them into text.
array_append, array_prepend, array_cat, array_remove
Adds, removes or concatenates elements in a PostgreSQL array.
array_to_string, array_position, cardinality
Turns an array into a string, looks for an element, or counts the elements.
ARRAY[], UNNEST, array_length
ARRAY[] builds an array literal. UNNEST expands an array into rows. array_length returns the length of an array for a given dimension.
AT TIME ZONE
Converts a timestamp into a different time zone.
Self-join
A self-join joins a table to itself. Handy for comparing rows of the same table.
AVG
AVG works out an average. Skips NULLs. Often what you want on price or basket size.
BOOL_AND and BOOL_OR
BOOL_AND returns TRUE when every row of a group satisfies a condition. BOOL_OR returns TRUE when at least one row does.
Working out the difference between dates
To get the gap between two dates: date1 - date2 gives days, AGE() gives a readable interval. PostgreSQL has no DATE_DIFF.
CAST and type conversion
CAST converts a value from one type to another. PostgreSQL also supports the :: syntax (e.g. '2024-01-01'::date).
CHECK
CHECK enforces a rule on a column. For instance: price > 0. Or: quantity > 0.
choosing between WHERE and HAVING
WHERE filters before aggregation, HAVING after. WHERE = raw rows. HAVING = aggregated results.
COALESCE and NULLIF
COALESCE returns the first argument that is not NULL. NULLIF returns NULL when both arguments are equal.
COUNT
COUNT counts rows. COUNT(*) counts every row. COUNT(col) skips NULLs.
CREATE INDEX and index types
Creates an index to speed queries up, with different types depending on the use case.
CREATE ROLE and ALTER ROLE
Creates and configures database roles, with their attributes and memberships.
CREATE SEQUENCE
Creates a sequential number generator, standalone or tied to a column.
CREATE TYPE ENUM
Creates an enumerated type with a fixed list of allowed values.
CREATE VIEW
A VIEW is a query saved as a virtual table. It simplifies complex queries and hides business logic away.
DATE_TRUNC – truncating a date
DATE_TRUNC truncates a date to the precision you want (month, week, year and so on). Essential for aggregating over time.
DELETE
DELETE removes rows. Without WHERE, you remove everything. Watch out for cascades (order_items, for instance).
EXCEPT and INTERSECT
EXCEPT returns the rows of the first SELECT that are absent from the second. INTERSECT returns the rows common to both SELECTs.
EXISTS / NOT EXISTS
EXISTS checks that a subquery returns at least one row. More efficient than IN on large tables.
EXPLAIN and EXPLAIN ANALYZE
Shows the execution plan of a query so you can spot the bottlenecks.
FILTER (WHERE) in aggregations
A conditional filter applied to one aggregate, leaving the other aggregates alone.
filters applied as early as possible
Filtering early cuts the volume of data down. WHERE before JOIN where you can. A filtered subquery before the join.
Date functions: NOW, CURRENT_DATE, EXTRACT
NOW() returns the current date and time. CURRENT_DATE returns today's date. EXTRACT pulls one part out of a date.
FULL OUTER JOIN
FULL OUTER JOIN returns every row from both tables, with NULL wherever there is no match.
granularity under control
Granularity = the level of detail of one row. An order is not an order line. A JOIN can change the granularity.
GROUP BY
GROUP BY groups rows so they can be aggregated. Every non-aggregated column has to be in the GROUP BY. Each group produces one row.
HAVING
HAVING filters groups, after aggregation. WHERE filters rows. HAVING filters aggregated results.
Indexes and query performance
Indexing strategies to tune PostgreSQL query performance.
INNER JOIN
INNER JOIN keeps only the matches. If nothing matches, the row disappears. Handy for linking facts to dimensions.
INSERT
INSERT adds rows to a table. It respects the constraints (NOT NULL, CHECK). Watch out for foreign keys.
data integrity
Data integrity keeps the data consistent. NOT NULL, UNIQUE, CHECK and FK constraints. It keeps orphans and inconsistent values out.
justified joins
Every JOIN should bring in information you actually use. A pointless JOIN weighs the query down and can create duplicates.
LEFT JOIN
LEFT JOIN keeps every row on the left. With no match, the right-hand columns come back NULL. Perfect for finding what is missing.
simple conditional logic (CASE WHEN)
CASE WHEN returns a different value depending on conditions, right inside the SELECT.
keeping duplicates in check
Duplicates skew your aggregates. A JOIN can multiply rows. Check with COUNT(*) against COUNT(DISTINCT).
MAX
MAX returns the largest value. Works on numbers and dates. Often paired with a filter.
MIN
MIN returns the smallest value. Works on numbers and dates. Handy for finding a first purchase.
The -> and ->> operators
-> returns a JSON sub-document, ->> returns text. #> and #>> give access by path. Used on JSONB columns.
JSONB containment operators: @>, <@, ?
Tests whether a JSON document contains another, is contained in it, or holds certain keys.
guarding against division by zero
A division by zero raises an error. Use NULLIF(x, 0) or CASE WHEN. Common in ratio calculations.
RANDOM and GENERATE_SERIES
RANDOM() produces a random number between 0 and 1. GENERATE_SERIES produces a series of numbers or dates with a given step.
REPLACE and SPLIT_PART
REPLACE swaps every occurrence of a substring. SPLIT_PART takes the nth part of a string split on a delimiter.
RETURNING
Returns the rows that were inserted, changed or removed, with no extra SELECT.
RIGHT JOIN
RIGHT JOIN keeps every row on the right. Usually avoidable: a LEFT JOIN the other way round does the job. Worth knowing to read existing SQL.
row_to_json and jsonb_each
Turns a row into JSON, or breaks a JSON object into key-value pairs.
STRING_AGG
STRING_AGG concatenates the values of a group into one string, separated by a delimiter. Supports ORDER BY inside the aggregate.
SUBSTRING and pulling text apart
SUBSTRING extracts part of a string. LEFT/RIGHT take from the ends. STRPOS finds the position of a substring.
SUM
SUM adds numeric values up. Often used with GROUP BY. Mind the types and the NULLs.
TO_CHAR – formatting a date as text
TO_CHAR formats a date (or a number) as a string, following a format mask.
TO_DATE and TO_TIMESTAMP – parsing a date
TO_DATE and TO_TIMESTAMP turn text into a date or a timestamp, following a given format.
to_jsonb, jsonb_build_object, jsonb_agg
to_jsonb turns a value into JSONB. jsonb_build_object builds a JSON object from key/value pairs. jsonb_agg aggregates rows into a JSON array.
TRUNCATE TABLE
Empties a whole table fast, with an option to reset the sequences.
UNIQUE
UNIQUE keeps duplicates out. Often used on email. It can span several columns.
UPDATE
UPDATE changes existing rows. Without WHERE, you change the whole table. Try it with a SELECT first.
no duplicated logic
Duplicated logic breeds bugs. Use CTEs to factor it out. One calculation, one place.
conditional aggregations
A conditional aggregation aggregates according to a condition. Usually through SUM(CASE WHEN ... THEN ... END). Lets you get several metrics from one query.
Rolling aggregates: SUM() OVER, AVG() OVER
SUM, AVG, COUNT, MIN and MAX can all be used as window functions, to build running totals or moving averages over N rows.
anti-join
An anti-join finds the rows with no match. Usually: LEFT JOIN + WHERE ... IS NULL. Handy for spotting inactive customers.
cardinalities
Cardinality describes the relationship between tables. 1-N: categories to products. 1-N: orders to order_items. Understanding it keeps duplicates away.
CREATE FUNCTION and CREATE PROCEDURE
Creates reusable stored functions and procedures inside the database.
CTE (WITH)
A CTE (WITH) is a readable temporary table. It improves clarity. It lets you break a query into steps.
DENSE_RANK
DENSE_RANK ranks with ties but no gaps. Two equal values get the same rank. The next rank does not skip.
Regular expressions: ~, REGEXP_REPLACE, REGEXP_MATCHES
~ and ~* filter with regular expressions. REGEXP_REPLACE swaps out what matches a pattern. REGEXP_MATCHES returns the captured groups.
FIRST_VALUE, LAST_VALUE, NTH_VALUE
FIRST_VALUE, LAST_VALUE and NTH_VALUE reach specific positions inside the window. Mind the default frame of LAST_VALUE.
Window frames: ROWS BETWEEN / RANGE BETWEEN
Pins down exactly which subset of rows a window function looks at.
GRANT
GRANT hands out privileges. It depends on the engine (PostgreSQL and so on). It matters in production.
multiple joins
A multiple join chains several tables together. Use short aliases. Check the granularity, or you will get duplicates.
jsonb_array_elements, jsonb_object_keys
jsonb_array_elements expands a JSON array into rows. jsonb_object_keys lists the keys of a JSON object. Set-returning functions, very handy for exploring JSON.
jsonb_set and jsonb_insert
jsonb_set changes a value inside a JSONB document. jsonb_insert inserts into a JSONB array. The path is given as a text[] array.
LAG
LAG reads the previous value in a given order. Very handy for comparing over time. Works with OVER (PARTITION BY ... ORDER BY ...).
LEAD
LEAD reads the next value in a given order. Handy for looking ahead to the next event. Same idea as LAG, but forwards.
advanced conditional logic
Conditional logic usually goes through CASE. It lets you build categories. Handy for reporting.
MERGE / UPSERT
An UPSERT inserts or updates depending on whether the row exists. Very handy on unique keys (customers.email, for instance). The syntax depends on the engine.
normalisation
Normalisation cuts down redundancy. You split the entities apart (customers, orders, items). You link them through keys.
NTILE
NTILE(n) spreads the rows over n equal groups following a given order. Ideal for building quartiles, deciles or percentiles.
ORDER BY (windowing)
Inside a window function, ORDER BY sets the order of the computation. It is not the final sort, even though the two are related. It drives LAG, LEAD and ranking.
OVER
OVER defines the window of an analytic function. No OVER, no window function. You set the order and/or the partitioning.
PARTITION BY
PARTITION BY splits the result into groups inside a window. Each group is handled on its own. Very handy per customer_id or category_id.
PERCENTILE_CONT and PERCENTILE_DISC
PERCENTILE_CONT computes an interpolated percentile, PERCENTILE_DISC returns the nearest existing value. A fraction of 0.5 gives the median.
permissions
Permissions control which actions are allowed. SELECT, INSERT, UPDATE, DELETE. They can be set per table.
PRIMARY KEY / FOREIGN KEY (recap)
PK/FK define the relational model. PK = identifier. FK = link. Without them, JOINs get risky.
least privilege
Least privilege = grant the bare minimum needed. It lowers the risk. It limits human error.
RANK
RANK ranks with ties, leaving gaps. Two equal values get the same rank. The next rank can skip a number.
a query that holds up against imperfect data
Real data is imperfect: NULLs, duplicates, outliers. Anticipate the edge cases with COALESCE and NULLIF.
nested queries
A nested query stacks levels on top of each other. Handy for: compute, then filter, then rank. A CTE is often easier to read.
REVOKE
REVOKE takes privileges away. The counterpart of GRANT. Handy once an access is no longer needed.
roles
A role bundles permissions together. Easier to manage. Fewer mistakes.
rollback
ROLLBACK undoes the changes of an uncommitted transaction. Handy while testing. It keeps accidental changes at bay.
Row Level Security (RLS)
Restricts access to the rows of a table per user, without changing the queries.
ROW_NUMBER
ROW_NUMBER numbers the rows inside a window. Handy for picking the "first" item of a group. Needs OVER(...).
separating environments
Keeping dev / staging / prod apart prevents accidents. Not everyone gets write access in production. Access is controlled by role.
keeping the solution simple
Simplicity is a quality of its own. No subquery where a JOIN will do. The simplest code is the most maintainable.
subqueries
A subquery is a query inside another one. It can sit in WHERE, FROM or SELECT. Handy when you want to filter by an aggregate.
STDDEV and VARIANCE
STDDEV measures the standard deviation and VARIANCE the variance of a set of values. The _POP variants work on the whole population, the _SAMP variants on a sample.
top N per group
Top N per group means keeping the N best rows of each partition. The classic answer: ROW_NUMBER, then filter on rn <= N.
traceable business logic
Traceability lets a reader understand the why. Explicit CTE names. Comments about the business, not the mechanics.
transactions
A transaction bundles several actions into one unit. Either all of it goes through, or none of it does. Handy for keeping data consistent.
UNION / UNION ALL
UNION combines two results and removes the duplicates. UNION ALL combines without removing them. The columns have to be compatible.
VACUUM and ANALYZE
Reclaims space and refreshes statistics to keep performance up.
Materialized views
A MATERIALIZED VIEW physically stores the result of a query. Faster, but it needs a REFRESH to stay up to date.
WITH in DML (data-modifying CTEs)
Use CTEs with INSERT/UPDATE/DELETE for complex changes that stay atomic.
Recursive CTE (WITH RECURSIVE)
WITH RECURSIVE allows recursive queries, handy for walking through hierarchies.