AdSense Placeholder
Slot: header_reference_page
SQL is the language for querying and changing relational databases. Every statement on this page was run in SQLite 3.46 against the small customers / orders schema created in the first section; where MySQL, PostgreSQL or SQL Server differ, the last section says so. Tidy long queries with the SQL Formatter.
Create tables
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
country TEXT DEFAULT 'US',
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
total NUMERIC NOT NULL CHECK (total >= 0),
status TEXT NOT NULL DEFAULT 'new'
);
SQLite only enforces REFERENCES (and so ON DELETE CASCADE) after PRAGMA foreign_keys = ON; on each connection; the other databases always enforce them.
| Statement |
Does |
ALTER TABLE customers ADD COLUMN phone TEXT; |
Adds a column |
ALTER TABLE customers RENAME COLUMN phone TO mobile; |
Renames a column |
ALTER TABLE customers DROP COLUMN mobile; |
Removes a column |
CREATE INDEX idx_orders_customer ON orders(customer_id); |
Speeds up lookups and joins on that column |
CREATE UNIQUE INDEX idx_email ON customers(email); |
Also enforces uniqueness |
DROP TABLE IF EXISTS old_orders; |
Deletes a table and its data |
CREATE VIEW big_orders AS SELECT * FROM orders WHERE total > 100; |
Saves a query as a virtual table |
Insert, update and delete
| Statement |
Does |
INSERT INTO customers (name, email) VALUES ('Ana', '[email protected]'); |
Adds one row |
INSERT INTO orders (customer_id, total) VALUES (1, 40), (1, 120), (2, 75); |
Adds several rows |
INSERT INTO archive SELECT * FROM orders WHERE status = 'done'; |
Copies rows from a query |
UPDATE orders SET status = 'paid' WHERE id = 2; |
Changes matching rows |
DELETE FROM orders WHERE status = 'cancelled'; |
Removes matching rows |
INSERT INTO customers (id, name, email) VALUES (1, 'Ana B.', '[email protected]') ON CONFLICT(id) DO UPDATE SET name = excluded.name; |
Upsert: insert, or update if the key exists |
INSERT INTO customers (name) VALUES ('Di') RETURNING id; |
Returns the new row's values (SQLite 3.35 and later) |
An UPDATE or DELETE without WHERE changes every row. Run the same WHERE in a SELECT first. Turn a spreadsheet export into INSERT statements with the CSV to SQL Converter.
Query
| Clause |
Example |
Notes |
SELECT |
SELECT name, email FROM customers; |
SELECT * returns every column |
WHERE |
WHERE country = 'US' AND total > 50 |
Filters rows before grouping |
IN, BETWEEN |
WHERE status IN ('new','paid'), WHERE total BETWEEN 50 AND 100 |
BETWEEN includes both ends |
LIKE |
WHERE email LIKE '%@example.com' |
% any text, _ one character |
IS NULL |
WHERE email IS NULL |
= NULL never matches |
ORDER BY |
ORDER BY total DESC, id |
Default is ascending |
LIMIT / OFFSET |
LIMIT 10 OFFSET 20 |
Rows 21 to 30 |
DISTINCT |
SELECT DISTINCT country FROM customers; |
Removes duplicate rows |
AS |
SELECT total * 1.2 AS gross FROM orders; |
Column or table alias |
CASE |
CASE WHEN total >= 100 THEN 'large' ELSE 'small' END |
Conditional value |
COALESCE |
COALESCE(email, 'none') |
First non-NULL argument |
The clauses always appear in this order: SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT.
Joins
| Join |
Returns |
INNER JOIN |
Only rows with a match on both sides |
LEFT JOIN |
Every row on the left, with NULLs where the right has no match |
RIGHT JOIN |
Every row on the right (SQLite 3.39 and later) |
FULL OUTER JOIN |
Every row from both sides (SQLite 3.39 and later; not in MySQL) |
CROSS JOIN |
Every combination of rows |
-- Customers with no orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
Grouping and aggregates
| Function |
Returns |
COUNT(*) |
Number of rows |
COUNT(col) |
Number of non-NULL values |
SUM(col), AVG(col) |
Total and mean (NULLs ignored) |
MIN(col), MAX(col) |
Smallest and largest value |
-- Revenue per customer, only customers above 100
SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 100
ORDER BY revenue DESC;
WHERE filters rows before grouping; HAVING filters the groups after.
Subqueries, CTEs and window functions
-- Orders above the average
SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders);
-- Common table expression
WITH revenue AS (
SELECT customer_id, SUM(total) AS amount FROM orders GROUP BY customer_id
)
SELECT c.name, r.amount FROM revenue r JOIN customers c ON c.id = r.customer_id;
-- Rank each customer's orders and keep a running total
SELECT customer_id, id, total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_in_customer,
SUM(total) OVER (ORDER BY id) AS running_total
FROM orders;
Transactions
| Statement |
Does |
BEGIN; |
Starts a transaction (START TRANSACTION in MySQL, BEGIN TRANSACTION in SQL Server) |
COMMIT; |
Makes the changes permanent |
ROLLBACK; |
Undoes everything since BEGIN |
SAVEPOINT s1; / ROLLBACK TO s1; |
Partial undo inside a transaction |
Dialect differences
| Task |
SQLite |
PostgreSQL |
MySQL / MariaDB |
SQL Server |
| First 10 rows |
LIMIT 10 |
LIMIT 10 |
LIMIT 10 |
SELECT TOP 10 ... or OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY |
| Auto-increment key |
INTEGER PRIMARY KEY |
GENERATED ALWAYS AS IDENTITY or SERIAL |
AUTO_INCREMENT |
IDENTITY(1,1) |
| Join strings |
a || b |
a || b |
CONCAT(a, b) |
a + b or CONCAT(a, b) |
| Upsert |
ON CONFLICT ... DO UPDATE |
ON CONFLICT ... DO UPDATE |
ON DUPLICATE KEY UPDATE |
MERGE |
| Return inserted row |
RETURNING |
RETURNING |
not supported (MariaDB: RETURNING) |
OUTPUT inserted.* |
| Current time |
CURRENT_TIMESTAMP, datetime('now') |
CURRENT_TIMESTAMP, now() |
CURRENT_TIMESTAMP, NOW() |
CURRENT_TIMESTAMP, GETDATE() |
| Quote an identifier |
"name" |
"name" |
`name` |
[name] |
| Case-insensitive match |
LIKE (ASCII only) |
ILIKE |
LIKE (with the default collations) |
LIKE (with the default collations) |
| Boolean type |
none: 0 and 1 |
BOOLEAN |
BOOLEAN (alias of TINYINT(1)) |
BIT |
Safety
Never build SQL by gluing user input into a string: that is how SQL injection happens. Use placeholders (?, $1, :name or %s, depending on the driver) and pass the values separately.