SQL Reference

v1.0.0

Browse SQL keywords with syntax, examples, and dialect notes for PostgreSQL, MySQL, and SQLite.

38 entries found

SELECTQueries

Retrieve rows from one or more tables.

Syntax

SELECT column1, column2 FROM table WHERE condition ORDER BY col LIMIT n;

Example

SELECT id, name, email FROM users WHERE active = true ORDER BY name LIMIT 10;

WHEREQueries

Filter rows based on a condition.

Syntax

SELECT ... FROM table WHERE condition;

Example

SELECT * FROM orders WHERE total > 100 AND status = 'shipped';

ORDER BYQueries

Sort result set. ASC is default; use DESC for descending.

Syntax

SELECT ... ORDER BY col [ASC|DESC], col2 [ASC|DESC];

Example

SELECT name, score FROM results ORDER BY score DESC, name ASC;

GROUP BYQueries

Group rows sharing a value, usually used with aggregate functions.

Syntax

SELECT col, AGG(col) FROM table GROUP BY col HAVING condition;

Example

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING COUNT(*) > 5;

HAVINGQueries

Filter groups after GROUP BY (like WHERE but for aggregates).

Syntax

SELECT ... GROUP BY col HAVING AGG(col) > value;

Example

SELECT category, SUM(revenue) AS total FROM sales GROUP BY category HAVING SUM(revenue) > 10000;

LIMIT / OFFSETQueries

Restrict number of rows returned and skip rows (pagination).

Syntax

SELECT ... LIMIT n OFFSET skip;

Example

SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40; -- page 3 of 20

📌 SQL Server: TOP n / OFFSET n ROWS FETCH NEXT n ROWS ONLY

DISTINCTQueries

Return unique values only.

Syntax

SELECT DISTINCT column FROM table;

Example

SELECT DISTINCT country FROM customers ORDER BY country;

SUBQUERYQueries

A query nested inside another query.

Syntax

SELECT ... WHERE col IN (SELECT col FROM table WHERE ...);

Example

SELECT name FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE location = 'Berlin');

CTE (WITH)Queries

Common Table Expression — a named temporary result set within a query.

Syntax

WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name;

Example

WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days') SELECT customer_id, COUNT(*) FROM recent GROUP BY customer_id;

WINDOWQueries

Perform calculations across a set of rows related to the current row.

Syntax

SELECT col, AGG(col) OVER (PARTITION BY col ORDER BY col) FROM table;

Example

SELECT name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank FROM employees;

INSERTDML

Add one or more rows to a table.

Syntax

INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4);

Example

INSERT INTO users (name, email, created_at) VALUES ('Alice', 'alice@example.com', NOW());

UPDATEDML

Modify existing rows. Always include WHERE unless updating all rows.

Syntax

UPDATE table SET col1 = v1, col2 = v2 WHERE condition;

Example

UPDATE products SET price = price * 1.1, updated_at = NOW() WHERE category = 'electronics';

DELETEDML

Remove rows from a table. Always include WHERE unless purging all rows.

Syntax

DELETE FROM table WHERE condition;

Example

DELETE FROM sessions WHERE expires_at < NOW();

UPSERTDML

Insert or update on conflict.

Syntax

INSERT INTO table (...) VALUES (...) ON CONFLICT (col) DO UPDATE SET ...;

Example

INSERT INTO settings (user_id, key, value) VALUES (1, 'theme', 'dark') ON CONFLICT (user_id, key) DO UPDATE SET value = EXCLUDED.value;

📌 MySQL: INSERT ... ON DUPLICATE KEY UPDATE. SQL Server: MERGE.

CREATE TABLEDDL

Create a new table with defined columns and constraints.

Syntax

CREATE TABLE name (col type [constraint], ...);

Example

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLEDDL

Modify an existing table structure.

Syntax

ALTER TABLE name ADD/DROP/MODIFY/RENAME ...;

Example

ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;
ALTER TABLE users DROP COLUMN legacy_field;
ALTER TABLE users RENAME COLUMN name TO full_name;

DROP TABLEDDL

Delete a table and all its data permanently.

Syntax

DROP TABLE [IF EXISTS] name [CASCADE];

Example

DROP TABLE IF EXISTS temp_data;
DROP TABLE orders CASCADE; -- also drops dependent objects

CREATE INDEXDDL

Create an index to speed up queries on specific columns.

Syntax

CREATE [UNIQUE] INDEX name ON table (col1, col2);

Example

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

TRUNCATEDDL

Remove all rows from a table quickly (faster than DELETE, resets sequences).

Syntax

TRUNCATE TABLE name [RESTART IDENTITY] [CASCADE];

Example

TRUNCATE TABLE audit_log RESTART IDENTITY;

INNER JOINJoins

Return rows with matching values in both tables.

Syntax

SELECT ... FROM a INNER JOIN b ON a.id = b.a_id;

Example

SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.total > 50;

LEFT JOINJoins

Return all rows from the left table, matched rows from the right (NULL if no match).

Syntax

SELECT ... FROM a LEFT JOIN b ON a.id = b.a_id;

Example

SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;

RIGHT JOINJoins

Return all rows from the right table, matched rows from the left.

Syntax

SELECT ... FROM a RIGHT JOIN b ON a.id = b.a_id;

Example

SELECT p.name, COUNT(oi.id) FROM order_items oi RIGHT JOIN products p ON oi.product_id = p.id GROUP BY p.id;

FULL OUTER JOINJoins

Return all rows from both tables, NULL where there is no match.

Syntax

SELECT ... FROM a FULL OUTER JOIN b ON a.id = b.a_id;

Example

SELECT a.id, b.id FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.a_id WHERE a.id IS NULL OR b.a_id IS NULL;

📌 MySQL: Simulate with LEFT JOIN UNION RIGHT JOIN.

COUNT / SUM / AVGFunctions

Aggregate functions — count rows, sum or average values.

Syntax

SELECT COUNT(*), SUM(col), AVG(col) FROM table;

Example

SELECT COUNT(*) AS total, SUM(amount) AS revenue, AVG(amount) AS aov FROM orders WHERE status = 'paid';

MIN / MAXFunctions

Return minimum and maximum values in a column.

Syntax

SELECT MIN(col), MAX(col) FROM table;

Example

SELECT MIN(price) AS cheapest, MAX(price) AS priciest FROM products WHERE active = true;

COALESCEFunctions

Return the first non-NULL value in the list.

Syntax

COALESCE(expr1, expr2, ... fallback)

Example

SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name FROM users;

NULLIFFunctions

Return NULL if both arguments are equal, else return the first argument.

Syntax

NULLIF(expr, compare_value)

Example

SELECT NULLIF(score, 0)  -- returns NULL instead of 0 division errors
SELECT 100 / NULLIF(total, 0) AS percentage FROM stats;

CAST / CONVERTFunctions

Convert a value from one data type to another.

Syntax

CAST(expr AS type) or expr::type (PostgreSQL)

Example

SELECT CAST('42' AS INTEGER) + 1;
SELECT price::NUMERIC(10,2) FROM products; -- PostgreSQL

📌 PostgreSQL: ::cast syntax. SQL Server: CONVERT(type, value, style).

STRING_AGGFunctions

Concatenate strings from a group into one string with separator.

Syntax

STRING_AGG(col, separator) [WITHIN GROUP (ORDER BY col)]

Example

SELECT dept, STRING_AGG(name, ', ' ORDER BY name) AS members FROM employees GROUP BY dept;

📌 MySQL: GROUP_CONCAT(). SQL Server: STRING_AGG() available in 2017+.

PRIMARY KEYConstraints

Uniquely identifies each row. Implies UNIQUE and NOT NULL.

Syntax

col type PRIMARY KEY or CONSTRAINT pk_name PRIMARY KEY (col1, col2)

Example

id SERIAL PRIMARY KEY
-- Composite:
CONSTRAINT pk_order_item PRIMARY KEY (order_id, product_id)

FOREIGN KEYConstraints

Enforces referential integrity between tables.

Syntax

col type REFERENCES table(col) [ON DELETE action]

Example

user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
-- Or named:
CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES users(id)

UNIQUEConstraints

Ensure all values in a column or set of columns are unique.

Syntax

col type UNIQUE or UNIQUE (col1, col2)

Example

email VARCHAR(255) UNIQUE NOT NULL
-- Composite unique:
UNIQUE (user_id, provider)

NOT NULLConstraints

Prevent NULL values in a column.

Syntax

col type NOT NULL [DEFAULT value]

Example

name TEXT NOT NULL DEFAULT ''
status TEXT NOT NULL CHECK (status IN ('active','inactive'))

CHECKConstraints

Enforce a condition on column values.

Syntax

col type CHECK (condition) or CONSTRAINT name CHECK (condition)

Example

price NUMERIC CHECK (price > 0)
CONSTRAINT valid_status CHECK (status IN ('pending','active','closed'))

LIKE / ILIKEOperators

Pattern matching with wildcards. % = any chars, _ = single char. ILIKE is case-insensitive (PostgreSQL).

Syntax

column LIKE 'pattern%'

Example

SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE name ILIKE '%phone%'; -- PostgreSQL

📌 MySQL: LIKE is case-insensitive on case-insensitive collations. Use BINARY for case-sensitive.

IN / NOT INOperators

Test if a value matches any value in a list or subquery.

Syntax

col IN (v1, v2, ...) or col IN (SELECT ...)

Example

SELECT * FROM orders WHERE status IN ('pending', 'processing');
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM bans);

BETWEENOperators

Test if a value is within a range (inclusive on both ends).

Syntax

col BETWEEN low AND high

Example

SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

IS NULL / IS NOT NULLOperators

Test for NULL values. Never use = NULL — always use IS NULL.

Syntax

col IS NULL | col IS NOT NULL

Example

SELECT * FROM users WHERE last_login IS NULL;
SELECT * FROM tasks WHERE completed_at IS NOT NULL;