SQL Quick Ref

SELECT skeleton

SELECT col1, col2, AGG(col3)
FROM table_name t
JOIN other o ON o.t_id = t.id
WHERE t.active = 1
GROUP BY col1, col2
HAVING AGG(col3) > 0
ORDER BY col1 ASC
LIMIT 50;

Joins at a glance

• INNER JOIN: only matches
• LEFT JOIN: keep all left rows
• CROSS JOIN: cartesian

-- LEFT JOIN to keep users even without orders
SELECT u.id, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Filtering patterns

-- IN list
SELECT * FROM products WHERE category IN ('book', 'toy');

-- Date range (inclusive start, exclusive end is often safer)
SELECT * FROM orders
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';

-- Search
SELECT * FROM users WHERE email LIKE '%example.com';

NULL safety

• Use IS NULL / IS NOT NULL
• Use COALESCE (or IFNULL/ISNULL per vendor) for fallback values

SELECT COALESCE(nickname, first_name) AS display_name
FROM users
WHERE deleted_at IS NULL;

Group by + having

SELECT customer_id, COUNT(*) AS n
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 3;

Insert / update / delete

-- INSERT
INSERT INTO users (id, email) VALUES (1, 'a@b.com');

-- UPDATE (always with WHERE)
UPDATE users SET email = 'new@b.com' WHERE id = 1;

-- DELETE (consider soft delete instead)
DELETE FROM users WHERE id = 1;

Upsert patterns

• MySQL: INSERT ... ON DUPLICATE KEY UPDATE
• PostgreSQL: INSERT ... ON CONFLICT ... DO UPDATE
• SQL Server: MERGE (use carefully)

-- MySQL example
INSERT INTO users (id, email)
VALUES (1, 'a@b.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

Constraints and indexes

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (id)
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

Transactions

START TRANSACTION;
-- do multiple related changes
COMMIT;  -- or ROLLBACK;

Performance reminders

• Index columns used in JOIN, WHERE, and common ORDER BY patterns.
• Avoid SELECT * in hot paths.
• Use EXPLAIN to see query plans.