Why does my query return NULL results unexpectedly?

Comparisons with NULL are never true. Use IS NULL and IS NOT NULL, and use COALESCE to replace NULL with a default value.

SQL Cheat Sheet: SELECT, Joins, Aggregates, and Data Changes

Introduction

SQL is the standard language for working with relational databases such as MySQL, PostgreSQL, and SQL Server. This cheat sheet uses standard SQL and notes common differences between databases. Always test changes on a copy of your data first.

Prerequisites

Access to any SQL database or an online SQL playground

Step-by-Step Guide

Step 1: Select, filter, and sort

Write clauses in this order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

SELECT id, name, price
FROM products
WHERE price > 10 AND category = 'books'
ORDER BY price DESC
LIMIT 10;                     -- SQL Server uses SELECT TOP 10 instead

WHERE name LIKE 'A%'          -- starts with A
WHERE id IN (1, 2, 3)
WHERE price BETWEEN 5 AND 20
WHERE email IS NULL           -- never use = NULL
SELECT DISTINCT category FROM products;

Step 2: Joins

INNER JOIN keeps matching rows only. LEFT JOIN keeps every row from the left table, with NULLs where nothing matches.

SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;   -- customers with no orders still appear

-- RIGHT JOIN mirrors LEFT; FULL OUTER JOIN keeps both sides (not in MySQL)

Step 3: Aggregates and grouping

WHERE filters rows before grouping, and HAVING filters groups after.

SELECT category,
       COUNT(*)   AS items,
       AVG(price) AS avg_price,
       MIN(price), MAX(price), SUM(price)
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY items DESC;

Step 4: Subqueries, CTEs, and CASE

A common table expression (WITH) names a subquery so it is easier to read. CASE adds conditional values.

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

WITH big_orders AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders GROUP BY customer_id
)
SELECT c.name, b.spent
FROM big_orders b JOIN customers c ON c.id = b.customer_id;

SELECT name,
  CASE WHEN price >= 100 THEN 'premium'
       WHEN price >= 20  THEN 'standard'
       ELSE 'budget' END AS tier
FROM products;

Step 5: Insert, update, and delete

Always include a WHERE clause on UPDATE and DELETE, and check it with a SELECT first.

INSERT INTO products (name, price, category)
VALUES ('SQL Basics', 19.99, 'books');

UPDATE products SET price = 24.99 WHERE id = 7;

DELETE FROM products WHERE id = 7;

-- preview what a change will touch
SELECT * FROM products WHERE id = 7;

Step 6: Create tables and indexes

Define keys and constraints so the database protects your data. Index columns you filter or join on often.

CREATE TABLE customers (
  id    INT PRIMARY KEY AUTO_INCREMENT,    -- PostgreSQL: GENERATED ALWAYS AS IDENTITY
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);

CREATE TABLE orders (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total       DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE INDEX idx_orders_customer ON orders (customer_id);
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

FAQs

Why does my query return NULL results unexpectedly?: Comparisons with NULL are never true. Use IS NULL and IS NOT NULL, and use COALESCE to replace NULL with a default value.

Summary

Write SELECT clauses in order, join with ON conditions, group and filter groups with HAVING, use CTEs for readability, and protect data with WHERE clauses on every change, constraints, and indexes.