SQL Reference

Quick reference for SQL keywords and functions

Data Query

SELECTSELECT col1, col2 FROM tableRetrieve rows from a table or view
WHEREWHERE conditionFilter rows by a condition
JOININNER JOIN t2 ON t1.id = t2.fkCombine rows from two tables on a condition
LEFT JOINLEFT JOIN t2 ON t1.id = t2.fkInclude all rows from the left table, NULLs for non-matching right rows
GROUP BYGROUP BY colGroup rows for aggregate functions
HAVINGHAVING COUNT(*) > 1Filter groups after GROUP BY
ORDER BYORDER BY col DESCSort result rows
LIMITLIMIT 10Restrict number of returned rows
DISTINCTSELECT DISTINCT colReturn unique values only
UNIONSELECT ... UNION SELECT ...Combine results of two queries, removing duplicates

Data Manipulation

INSERTINSERT INTO t (col) VALUES ('val')Insert new rows
UPDATEUPDATE t SET col = 'val' WHERE id = 1Modify existing rows
DELETEDELETE FROM t WHERE id = 1Remove rows
TRUNCATETRUNCATE TABLE tRemove all rows quickly (DDL in some databases)

Data Definition

CREATE TABLECREATE TABLE t (id INT NOT NULL)Create a new table
ALTER TABLEALTER TABLE t ADD COLUMN col INTModify an existing table structure
DROP TABLEDROP TABLE tPermanently delete a table
CREATE INDEXCREATE INDEX idx ON t (col)Create an index to speed up queries
PRIMARY KEYid INT PRIMARY KEYUniquely identifies each row
FOREIGN KEYFOREIGN KEY (col) REFERENCES t2(id)Enforces referential integrity
NOT NULLColumn must have a value
UNIQUEAll values in the column must be distinct
DEFAULTcol INT DEFAULT 0Fallback value when none is supplied

Aggregate Functions

COUNT(*)Number of rows in a group
SUM(col)Sum of numeric values
AVG(col)Average of numeric values
MIN(col)Smallest value
MAX(col)Largest value

Common Data Types

INT / INTEGERWhole numbers (4 bytes)
BIGINTLarge whole numbers (8 bytes)
VARCHAR(n)Variable-length string up to n characters
TEXTUnlimited-length string
BOOLEANTrue/false value
DECIMAL(p,s)Exact numeric with precision p and scale s
TIMESTAMPDate and time value
JSON / JSONBJSON document (JSONB is binary-indexed in PostgreSQL)

Transactions

BEGINStart a transaction block
COMMITSave all changes in the current transaction
ROLLBACKUndo all changes since BEGIN
SAVEPOINTSAVEPOINT sp1Mark a point within a transaction to roll back to