SQL cheat sheet

Logical Execution Order

SQL clauses are written in one order but evaluated in another. Knowing this prevents most alias and filter bugs.

-- Written order vs. evaluation order
-- 1. FROM + JOINs          -- build the working rowset
-- 2. WHERE                 -- filter rows (no aggregates yet)
-- 3. GROUP BY              -- bucket rows
-- 4. HAVING                -- filter buckets (aggregates allowed)
-- 5. SELECT                -- compute output columns + aliases
-- 6. DISTINCT              -- remove duplicates
-- 7. ORDER BY              -- sort (aliases allowed here)
-- 8. LIMIT / OFFSET        -- slice result

SELECT / WHERE / ORDER BY / LIMIT

-- Basic shape
SELECT
    col1,
    col2,
    col1 * 2          AS doubled,
    UPPER(col3)       AS upper_col
FROM   schema.table
WHERE  status = 'active'
  AND  created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT  100 OFFSET 200;   -- OFFSET skips rows; use for pagination

-- DISTINCT
SELECT DISTINCT department FROM employees;

-- Aliases do NOT exist in WHERE (evaluated before SELECT)
-- Use a subquery or CTE when you need to filter on a computed alias
SELECT * FROM (
    SELECT id, salary * 1.1 AS adj FROM employees
) sub
WHERE adj > 60000;

Operators and Filters

-- Comparison
WHERE age BETWEEN 25 AND 40          -- inclusive on both ends
WHERE country IN ('US', 'CA', 'MX')
WHERE country NOT IN ('XX', 'YY')
WHERE name LIKE 'J%'                 -- % = any sequence, _ = one char
WHERE name ILIKE 'j%'                -- case-insensitive; Postgres only
WHERE email IS NULL
WHERE email IS NOT NULL

-- Pattern tips
WHERE code LIKE 'A_C%'   -- starts with A, any char, then C, then anything

-- Boolean
WHERE is_active = TRUE AND (role = 'admin' OR level >= 5)

-- Regex (Postgres)
WHERE name ~ '^[A-Z]'    -- case-sensitive match
WHERE name ~* '^[a-z]'   -- case-insensitive match

-- ANY / ALL
WHERE salary > ANY (SELECT salary FROM managers)
WHERE salary > ALL (SELECT salary FROM directors)

-- EXISTS (stops at first match — often faster than IN on large sets)
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
)

JOINs

-- INNER JOIN — only matching rows on both sides
SELECT e.name, d.name AS dept
FROM   employees e
JOIN   departments d ON e.dept_id = d.id;

-- LEFT JOIN — all left rows; NULL for unmatched right columns
SELECT e.name, o.order_id
FROM   customers e
LEFT JOIN orders o ON o.customer_id = e.id;

-- Find customers with NO orders (anti-join pattern)
SELECT e.name
FROM   customers e
LEFT JOIN orders o ON o.customer_id = e.id
WHERE  o.order_id IS NULL;

-- RIGHT JOIN — all right rows; NULL for unmatched left columns
-- (usually rewrite as LEFT JOIN with tables swapped — cleaner)
SELECT o.order_id, e.name
FROM   orders o
RIGHT JOIN customers e ON o.customer_id = e.id;

-- FULL OUTER JOIN — all rows from both sides; NULL where no match
SELECT a.id, b.id
FROM   table_a a
FULL JOIN table_b b ON a.key = b.key;

-- CROSS JOIN — every combination (Cartesian product); rows_a * rows_b
SELECT a.size, b.color
FROM   sizes a
CROSS JOIN colors b;

-- SELF JOIN — join a table to itself; requires distinct aliases
SELECT e.name AS employee, m.name AS manager
FROM   employees e
JOIN   employees m ON e.manager_id = m.id;

-- LATERAL / CROSS APPLY (Postgres LATERAL, SQL Server CROSS APPLY)
-- Right side can reference left-side columns
SELECT c.name, recent.order_date
FROM   customers c
CROSS JOIN LATERAL (
    SELECT order_date FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 1
) recent;

GROUP BY, HAVING, and Aggregates

-- Core aggregates
SELECT
    dept_id,
    COUNT(*)                     AS headcount,     -- includes NULLs in count of rows
    COUNT(bonus)                 AS has_bonus,     -- NULLs excluded
    COUNT(DISTINCT job_title)    AS unique_titles,
    SUM(salary)                  AS total_salary,
    AVG(salary)                  AS avg_salary,
    MIN(salary)                  AS min_salary,
    MAX(salary)                  AS max_salary
FROM   employees
GROUP BY dept_id;

-- HAVING filters after aggregation (WHERE runs before)
SELECT dept_id, COUNT(*) AS cnt
FROM   employees
GROUP BY dept_id
HAVING COUNT(*) > 10
ORDER BY cnt DESC;

-- GROUP BY ALL columns not aggregated (or use ordinal)
SELECT year, month, SUM(revenue)
FROM   sales
GROUP BY year, month;       -- explicit (preferred)
-- GROUP BY 1, 2;            -- ordinal shorthand (fragile, avoid in production)

-- ROLLUP — subtotals + grand total (ANSI SQL)
SELECT dept, job, SUM(salary)
FROM   employees
GROUP BY ROLLUP(dept, job);

-- CUBE — all combinations of subtotals
GROUP BY CUBE(dept, job, year);

-- GROUPING SETS — specify exactly which groups you want
GROUP BY GROUPING SETS ((dept), (job), ());

Subqueries

-- Scalar subquery in SELECT (must return exactly one row/col)
SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

-- Correlated subquery — references outer query row by row (can be slow)
SELECT e.name, e.salary
FROM   employees e
WHERE  e.salary > (
    SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id
);

-- Derived table (inline view) in FROM
SELECT dept_id, avg_sal
FROM (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM   employees
    GROUP BY dept_id
) dept_avgs
WHERE avg_sal > 70000;

-- Subquery with IN
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 1000);

CTEs (Common Table Expressions)

-- Basic CTE — readable alternative to derived tables
WITH high_earners AS (
    SELECT id, name, salary
    FROM   employees
    WHERE  salary > 100000
),
dept_counts AS (
    SELECT dept_id, COUNT(*) AS cnt
    FROM   employees
    GROUP BY dept_id
)
SELECT h.name, h.salary, d.cnt
FROM   high_earners h
JOIN   dept_counts d ON h.dept_id = d.dept_id;

-- Multiple CTEs are separated by commas, one WITH keyword only

-- Recursive CTE — traverses hierarchies / generates series
-- Postgres, SQL Server, SQLite, MySQL 8+
WITH RECURSIVE org_chart AS (
    -- Anchor: top-level (no manager)
    SELECT id, name, manager_id, 1 AS depth
    FROM   employees
    WHERE  manager_id IS NULL

    UNION ALL

    -- Recursive member: join to previous level
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM   employees e
    JOIN   org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

-- Generate a date series (Postgres)
WITH RECURSIVE dates AS (
    SELECT '2024-01-01'::date AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < '2024-01-31'
)
SELECT d FROM dates;

Window Functions

Window functions operate across rows related to the current row without collapsing the rowset (unlike GROUP BY).

-- Syntax template
function_name(expr) OVER (
    PARTITION BY col      -- optional: reset per group
    ORDER BY   col        -- required for ranking/running calcs
    ROWS/RANGE BETWEEN ... AND ...  -- optional frame
)

Ranking

SELECT
    name,
    dept_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
      -- unique sequential integer; no ties
    RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
      -- ties get same rank; next rank skips (1,1,3)
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drnk,
      -- ties get same rank; no gaps (1,1,2)
    NTILE(4)     OVER (ORDER BY salary DESC)                      AS quartile
FROM employees;

Offset Functions

SELECT
    order_date,
    revenue,
    LAG(revenue, 1, 0)  OVER (ORDER BY order_date) AS prev_rev,
      -- value from N rows before; 3rd arg = default when no row exists
    LEAD(revenue, 1, 0) OVER (ORDER BY order_date) AS next_rev,
    FIRST_VALUE(revenue) OVER (ORDER BY order_date
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_rev,
    LAST_VALUE(revenue)  OVER (ORDER BY order_date
                               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_rev
FROM daily_sales;

Running Totals and Moving Averages

SELECT
    order_date,
    revenue,
    SUM(revenue) OVER (ORDER BY order_date
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    AVG(revenue) OVER (ORDER BY order_date
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)          AS rolling_7d_avg,
    SUM(revenue) OVER (PARTITION BY EXTRACT(YEAR FROM order_date)
                        ORDER BY order_date
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS ytd_total
FROM daily_sales;

Frame Clause Reference

-- ROWS  — physical row count (ignores ties)
-- RANGE — logical value range (groups ties together)

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- cumulative
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING           -- centered window
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   -- suffix total
RANGE BETWEEN INTERVAL '7' DAY PRECEDING            -- date-aware (Postgres)
          AND CURRENT ROW

Distribution Functions

SELECT
    score,
    PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,   -- 0..1
    CUME_DIST()    OVER (ORDER BY score) AS cum_dist     -- 0..1 inclusive
FROM results;

CASE Expressions

-- Searched CASE (most flexible)
SELECT
    name,
    salary,
    CASE
        WHEN salary >= 120000 THEN 'Senior'
        WHEN salary >= 80000  THEN 'Mid'
        ELSE                       'Junior'
    END AS band
FROM employees;

-- Simple CASE (equality only)
SELECT
    status,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        ELSE          'Unknown'
    END AS status_label
FROM accounts;

-- CASE inside aggregate (conditional count / pivot)
SELECT
    dept_id,
    COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
    COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
    AVG(CASE WHEN job_level >= 3 THEN salary END) AS senior_avg_sal
FROM employees
GROUP BY dept_id;

NULL Handling

-- NULLs propagate: NULL + 5 = NULL, NULL = NULL is UNKNOWN (not TRUE)
-- Always use IS NULL / IS NOT NULL, not = NULL

-- COALESCE — first non-NULL argument
SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM users;

-- NULLIF — returns NULL when two values are equal (avoids division by zero)
SELECT revenue / NULLIF(units_sold, 0) AS avg_price
FROM sales;

-- NULLIF in aggregates: treat sentinel values as NULL
SELECT AVG(NULLIF(score, -1)) FROM results;

-- NULL in aggregates: SUM/AVG/MIN/MAX ignore NULLs; COUNT(*) counts all rows
SELECT COUNT(*), COUNT(email) FROM users;  -- differ when email has NULLs

-- Postgres: use IS DISTINCT FROM to compare NULLs safely
WHERE a IS DISTINCT FROM b       -- true even when both are NULL
WHERE a IS NOT DISTINCT FROM b   -- true when both NULL or both equal

Set Operations

-- UNION — combine results, remove duplicates (sorts internally — slower)
SELECT id FROM table_a
UNION
SELECT id FROM table_b;

-- UNION ALL — combine results, keep duplicates (faster; prefer when dupes OK)
SELECT 'revenue' AS type, amount FROM sales
UNION ALL
SELECT 'refund',          amount FROM refunds;

-- INTERSECT — rows present in BOTH results
SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;

-- EXCEPT (MINUS in Oracle) — rows in first NOT in second
SELECT email FROM all_users
EXCEPT
SELECT email FROM unsubscribed;

-- Rules: same number of columns, compatible types; column names from first query

INSERT / UPDATE / DELETE / UPSERT

-- INSERT — single row
INSERT INTO products (name, price, category)
VALUES ('Widget', 9.99, 'tools');

-- INSERT — multiple rows
INSERT INTO products (name, price, category) VALUES
    ('Gadget', 19.99, 'electronics'),
    ('Doohickey', 4.99, 'tools');

-- INSERT FROM SELECT
INSERT INTO archive_orders
SELECT * FROM orders WHERE created_at < '2023-01-01';

-- UPDATE
UPDATE employees
SET    salary     = salary * 1.10,
       updated_at = CURRENT_TIMESTAMP
WHERE  dept_id = 3 AND performance_rating = 'A';

-- UPDATE with JOIN (Postgres)
UPDATE employees e
SET    salary = e.salary * 1.05
FROM   departments d
WHERE  e.dept_id = d.id
  AND  d.name = 'Engineering';

-- DELETE
DELETE FROM sessions WHERE expires_at < NOW();

-- TRUNCATE — removes all rows, faster than DELETE, cannot be filtered
TRUNCATE TABLE log_events;           -- Postgres: add RESTART IDENTITY to reset sequences

-- UPSERT — Postgres (ON CONFLICT)
INSERT INTO user_stats (user_id, logins)
VALUES (42, 1)
ON CONFLICT (user_id)
DO UPDATE SET logins = user_stats.logins + 1,
              last_seen = NOW();

-- UPSERT — MySQL (ON DUPLICATE KEY)
INSERT INTO user_stats (user_id, logins)
VALUES (42, 1)
ON DUPLICATE KEY UPDATE logins = logins + 1;

-- MERGE (ANSI SQL; Postgres does not support MERGE pre-v15; use on SQL Server / Oracle)
MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED     THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

DDL — CREATE TABLE and Indexes

-- CREATE TABLE with common constraints
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,          -- Postgres auto-increment; MySQL uses AUTO_INCREMENT
    customer_id INT          NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
    amount      NUMERIC(12,2) CHECK (amount >= 0),
    note        TEXT,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),   -- with time zone (Postgres)
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- ALTER TABLE
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ;
ALTER TABLE orders DROP COLUMN note;
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';  -- Postgres
ALTER TABLE orders RENAME COLUMN status TO order_status;

-- DROP
DROP TABLE IF EXISTS temp_staging;

-- INDEXES
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_created  ON orders (created_at DESC);

-- Composite index — order matters; leftmost prefix is used
CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Partial index (Postgres) — index only a subset of rows
CREATE INDEX idx_active_orders ON orders (customer_id) WHERE status = 'active';

-- Full-text index (MySQL)
CREATE FULLTEXT INDEX idx_ft_body ON articles (body);

Date and Time Functions

-- Current date/time
SELECT CURRENT_DATE;              -- ANSI; returns date only
SELECT CURRENT_TIMESTAMP;         -- ANSI; date + time + tz
SELECT NOW();                     -- Postgres + MySQL alias for CURRENT_TIMESTAMP
SELECT GETDATE();                 -- SQL Server

-- Extract parts
SELECT EXTRACT(YEAR  FROM created_at) AS yr,     -- ANSI
       EXTRACT(MONTH FROM created_at) AS mo,
       EXTRACT(DOW   FROM created_at) AS day_of_week;  -- Postgres: 0=Sun

SELECT DATE_PART('hour', created_at);             -- Postgres equivalent
SELECT YEAR(created_at), MONTH(created_at);       -- MySQL shorthand

-- Truncate to period (Postgres)
SELECT DATE_TRUNC('month', created_at);   -- first instant of the month
SELECT DATE_TRUNC('week',  created_at);

-- MySQL equivalent
SELECT DATE_FORMAT(created_at, '%Y-%m-01');

-- Arithmetic
SELECT created_at + INTERVAL '7 days'   FROM orders;   -- Postgres
SELECT created_at + INTERVAL 7 DAY      FROM orders;   -- MySQL
SELECT DATEADD(day, 7, created_at)      FROM orders;   -- SQL Server

-- Difference between dates
SELECT AGE(end_date, start_date)                      -- Postgres; returns interval
SELECT DATEDIFF(end_date, start_date)                 -- MySQL; days
SELECT DATEDIFF(day, start_date, end_date)            -- SQL Server

-- Format (Postgres)
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS');

-- Format (MySQL)
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s');

String Functions

-- Concatenation
SELECT first_name || ' ' || last_name AS full_name;          -- ANSI / Postgres
SELECT CONCAT(first_name, ' ', last_name);                   -- MySQL + Postgres
SELECT CONCAT_WS(', ', last_name, first_name);               -- with separator

-- Case
SELECT UPPER(name), LOWER(name), INITCAP(name);  -- INITCAP: Postgres / Oracle

-- Length
SELECT LENGTH(name);            -- character length (Postgres / MySQL)
SELECT CHAR_LENGTH(name);       -- synonym; safer for multibyte

-- Trim and pad
SELECT TRIM('  hello  ');       -- both sides
SELECT LTRIM(name), RTRIM(name);
SELECT LPAD(code, 5, '0');      -- left-pad to width 5 with '0'
SELECT RPAD(code, 5, '-');

-- Substring
SELECT SUBSTRING(name, 1, 3);      -- ANSI; 1-based indexing
SELECT SUBSTR(name, 1, 3);         -- alias (MySQL / Oracle)
SELECT LEFT(name, 3);              -- MySQL + SQL Server
SELECT RIGHT(name, 3);

-- Position
SELECT POSITION('at' IN 'data');   -- ANSI; returns 2
SELECT STRPOS('data', 'at');       -- Postgres

-- Replace
SELECT REPLACE(phone, '-', '');

-- Split (Postgres)
SELECT SPLIT_PART('a,b,c', ',', 2);   -- returns 'b'

-- Regex replace / extract (Postgres)
SELECT REGEXP_REPLACE(name, '[^a-z]', '', 'gi');
SELECT REGEXP_MATCHES(text_col, '(\d+)', 'g');   -- returns array of matches

-- Type casting
SELECT '42'::INT;                              -- Postgres cast syntax
SELECT CAST('42' AS INT);                      -- ANSI
SELECT CONVERT(INT, '42');                     -- SQL Server

Common Interview Patterns

Second Highest Value

-- Method 1: OFFSET (simple, Postgres/MySQL)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Method 2: subquery (portable)
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 3: dense_rank (handles NULLs, ties cleanly)
SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
    FROM employees
) ranked
WHERE dr = 2
LIMIT 1;

Deduplicate with ROW_NUMBER

-- Keep the most-recently-inserted duplicate per email
WITH deduped AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY email
               ORDER BY created_at DESC
           ) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (
    SELECT id FROM deduped WHERE rn > 1
);

-- Or: SELECT only the deduplicated rows (no delete)
SELECT * FROM deduped WHERE rn = 1;

Top-N per Group

-- Top 3 earners per department
SELECT dept_id, name, salary
FROM (
    SELECT
        dept_id, name, salary,
        ROW_NUMBER() OVER (
            PARTITION BY dept_id
            ORDER BY salary DESC
        ) AS rn
    FROM employees
) ranked
WHERE rn <= 3;

Running Percentage of Total

SELECT
    category,
    revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM category_revenue
ORDER BY revenue DESC;

Gap and Island Detection

-- Find start/end of consecutive date sequences per user
SELECT
    user_id,
    MIN(activity_date) AS island_start,
    MAX(activity_date) AS island_end
FROM (
    SELECT
        user_id,
        activity_date,
        activity_date - ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY activity_date
        ) * INTERVAL '1 day' AS grp   -- Postgres
    FROM user_activity
) grouped
GROUP BY user_id, grp
ORDER BY user_id, island_start;

Pivot Without PIVOT Syntax

-- Quarter revenue pivoted into columns using CASE
SELECT
    product_id,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue END) AS q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue END) AS q4
FROM quarterly_revenue
GROUP BY product_id;

Median (ANSI percentile)

-- Postgres
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

-- MySQL 8+ and SQL Server also support PERCENTILE_CONT

Transactions and Isolation

-- Explicit transaction block
BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

-- Roll back on error
BEGIN;
    -- ... statements ...
ROLLBACK;   -- undo everything since BEGIN

-- Savepoints — partial rollback within a transaction
BEGIN;
    INSERT INTO audit_log VALUES (1, NOW());
    SAVEPOINT sp1;
    UPDATE risky_table SET val = 0;
    ROLLBACK TO SAVEPOINT sp1;   -- undoes UPDATE, keeps INSERT
COMMIT;

-- Isolation levels (ANSI) — set before or at transaction start
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- default in Postgres / SQL Server
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;   -- MySQL default; prevents non-repeatable reads
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;      -- strictest; prevents all anomalies

Quick Reference — Operator and Function Summary

-- Arithmetic         +  -  *  /  %  ^(Postgres power)
-- Comparison         =  <>  !=  <  >  <=  >=
-- Logical            AND  OR  NOT
-- String concat      ||  (ANSI)    CONCAT() (MySQL/Postgres)
-- Membership         IN  NOT IN  BETWEEN  LIKE  ILIKE(Postgres)
-- Existence          EXISTS  NOT EXISTS
-- NULL checks        IS NULL  IS NOT NULL  COALESCE  NULLIF

-- Aggregate          COUNT  SUM  AVG  MIN  MAX
--                    STRING_AGG(col, ',') -- Postgres; GROUP_CONCAT in MySQL
--                    ARRAY_AGG(col)       -- Postgres only

-- Casting            CAST(x AS type)   x::type (Postgres)

-- Conditional        CASE WHEN ... THEN ... ELSE ... END
--                    IIF(cond, a, b)   -- SQL Server
--                    IF(cond, a, b)    -- MySQL

-- Set ops            UNION ALL  UNION  INTERSECT  EXCEPT (MINUS in Oracle)

-- Window             ROW_NUMBER  RANK  DENSE_RANK  NTILE
--                    LAG  LEAD  FIRST_VALUE  LAST_VALUE  NTH_VALUE
--                    SUM  AVG  COUNT  MIN  MAX  (all work as window fns too)