Query Best Practices

-- Reads all columns even if only 2 are used
SELECT *
FROM orders
WHERE order_status = 'Delivered';

-- application code then does:
-- order.order_id, order.customer_id
-- all other columns: wasted I/O

-- Only read what you need
SELECT
  order_id,
  customer_id,
  total_amount,
  delivery_date
FROM orders
WHERE order_status = 'Delivered';

-- Non-SARGable: function applied to indexed column
-- Full scan even with index on order_date
WHERE strftime('%Y', order_date) = '2024'

WHERE UPPER(city) = 'MUMBAI'

WHERE LENGTH(product_name) > 10

WHERE total_amount * 1.18 > 1000

-- SARGable: transform the literal, not the column
-- Index on order_date can be used
WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01'

WHERE city = 'New York'   -- or store consistently-cased data

WHERE product_name > ''  -- different logic needed

WHERE total_amount > 847.46  -- pre-compute: 1000 / 1.18

-- Full scan: leading wildcard prevents index use
WHERE product_name LIKE '%milk%'
WHERE email LIKE '%@gmail.com'

-- Index usable: trailing wildcard only
WHERE product_name LIKE 'Amul%'
WHERE email LIKE 'rahul%'
-- For contains-search: use full-text indexes

-- Non-SARGable JOIN: function kills the FK index
SELECT o.order_id, c.first_name
FROM orders    AS o
JOIN customers AS c
  ON CAST(o.customer_id AS TEXT)
   = CAST(c.customer_id AS TEXT);
-- Computes CAST for every row pair — full scan

-- SARGable JOIN: same types, direct comparison
SELECT o.order_id, c.first_name
FROM orders    AS o
JOIN customers AS c
  ON o.customer_id = c.customer_id;
-- Index on customer_id (FK) is used

-- Implicit type coercion: date stored as TEXT
-- WHERE CAST(order_date AS DATE) = '2024-03-15'
-- or: WHERE date(order_date) = '2024-03-15'
-- Function on column = no index

-- Store dates as DATE/TEXT in ISO format: 'YYYY-MM-DD'
-- Then direct comparison works and is SARGable:
WHERE order_date = '2024-03-15'

-- Ambiguous: which table does customer_id belong to?
SELECT order_id, customer_id, first_name, total_amount
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE order_status = 'Delivered';
-- 'customer_id' is ambiguous in SELECT — whose?

-- Clear: every column is table-qualified
SELECT
  o.order_id,
  o.customer_id,
  c.first_name,
  o.total_amount
FROM orders    AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.order_status = 'Delivered';

-- t1/t2 aliases: meaningless, hard to review
SELECT t1.order_id, t2.product_name, t1.quantity
FROM order_items t1
JOIN products t2 ON t1.product_id = t2.product_id;

-- Semantic aliases: self-documenting
SELECT
  oi.order_id,
  p.product_name,
  oi.quantity,
  oi.line_total
FROM order_items AS oi
JOIN products    AS p ON oi.product_id = p.product_id;

-- Scans all matching rows to get the count
SELECT customer_id
FROM customers
WHERE (
  SELECT COUNT(*)
  FROM orders
  WHERE orders.customer_id = customers.customer_id
    AND order_status = 'Delivered'
) > 0;

-- Stops at the first matching row — much faster
SELECT customer_id
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.order_status = 'Delivered'
);

-- Checking if a product has any orders
SELECT product_id
FROM products
WHERE (
  SELECT COUNT(*) FROM order_items
  WHERE order_items.product_id = products.product_id
) > 0;

-- EXISTS is always better for "does any row match?"
SELECT product_id
FROM products AS p
WHERE EXISTS (
  SELECT 1 FROM order_items AS oi
  WHERE oi.product_id = p.product_id
);

-- Both of these return 0 rows — not the rows with NULL delivery_date
SELECT * FROM orders WHERE delivery_date = NULL;
SELECT * FROM orders WHERE delivery_date != NULL;
-- = NULL and != NULL are always NULL (falsy), never TRUE

-- IS NULL and IS NOT NULL are the correct tests
SELECT * FROM orders WHERE delivery_date IS NULL;
SELECT * FROM orders WHERE delivery_date IS NOT NULL;

-- AVG ignores NULL rows — result looks like average of all,
-- but it is only the average of non-NULL rows
SELECT AVG(delivery_date) FROM orders;
-- If 20% of rows have NULL delivery_date, the average
-- is computed over only 80% of rows — no warning given

-- Count NULLs explicitly to know what AVG is actually over
SELECT
  COUNT(*)                 AS total_orders,
  COUNT(delivery_date)     AS orders_with_delivery,
  COUNT(*) - COUNT(delivery_date) AS orders_missing_delivery
FROM orders;

-- NULL propagates: any arithmetic with NULL = NULL
SELECT
  first_name,
  discount_pct * total_amount AS discount_amount
-- If discount_pct is NULL → discount_amount is NULL
-- Report shows NULL instead of 0

-- Replace NULL with a sensible default before arithmetic
SELECT
  first_name,
  COALESCE(discount_pct, 0) * total_amount AS discount_amount
-- COALESCE returns first non-NULL argument
-- NULL discount_pct treated as 0 → correct ₹0 discount

-- Selecting non-aggregated columns not in GROUP BY
-- (Some databases allow this; result is non-deterministic)
SELECT customer_id, first_name, SUM(total_amount)
FROM orders
GROUP BY customer_id;
-- first_name is not in GROUP BY — which first_name
-- does the database pick? Undefined.

SELECT customer_id, first_name, SUM(total_amount) AS revenue
FROM orders AS o
JOIN customers AS c USING (customer_id)
GROUP BY o.customer_id, c.first_name;
-- Every selected column is either aggregated or in GROUP BY

-- Runs a subquery for EVERY row in orders — O(n) queries
SELECT
  order_id,
  (SELECT first_name FROM customers
   WHERE customer_id = o.customer_id) AS name
FROM orders AS o;

-- One JOIN — single pass
SELECT o.order_id, c.first_name AS name
FROM orders    AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

-- DISTINCT hides the JOIN that is producing duplicates
SELECT DISTINCT c.customer_id, c.first_name
FROM customers AS c
JOIN orders    AS o ON o.customer_id = c.customer_id;
-- Why are there duplicates? DISTINCT masks the root cause

-- Use EXISTS to find customers with at least one order
SELECT c.customer_id, c.first_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

-- OR prevents index use when columns are different
WHERE city = 'New York' OR store_id = 'ST001'
-- Database cannot satisfy both sides with one index scan

-- UNION ALL splits into two index-friendly queries
SELECT * FROM customers WHERE city = 'New York'
UNION ALL
SELECT * FROM customers WHERE store_id = 'ST001'
  AND city != 'New York';  -- avoid duplicates

SARGable (Search ARGument Able) describes a WHERE clause condition that allows the database engine to use an index to satisfy it. A condition is SARGable when the indexed column appears alone on one side of the comparison, without any function or transformation applied to it. The database can walk the sorted index to find matching rows rather than scanning the full table.

A condition becomes non-SARGable when a function is applied to the indexed column: WHERE YEAR(order_date) = 2024, WHERE UPPER(city) = 'MUMBAI', or WHERE total_amount * 1.18 > 1000. The database cannot use the index on order_date for the first example because the index stores raw dates, not YEAR-extracted integers. The engine must compute YEAR() for every row and compare — a full table scan.

Fix: transform the comparison value instead of the column. Replace WHERE YEAR(order_date) = 2024 with WHERE order_date > '2024-01-01' AND order_date < '2025-01-01'. The column appears unmodified, the index can be used. SARGability matters because on tables with millions of rows, the difference between an index seek and a full scan is measured in seconds vs. milliseconds. Always check EXPLAIN QUERY PLAN to verify index usage.

SELECT * has three production problems. First, it reads every column from disk even if your application only uses two — unnecessary I/O, more data transferred over the network, more memory used. On wide tables (100+ columns, JSONB blobs, large TEXT fields) this overhead is significant. Second, SELECT * is fragile: when a column is added to the table, SELECT * returns it even if the application does not expect it, breaking deserialization code, ORMs that map columns to object fields, or CSV exports with fixed column order. Third, it makes queries unreadable — a reader cannot determine what data the query produces without knowing the table schema.

The only acceptable context for SELECT * is interactive exploration in a SQL client — never in views, stored procedures, application code, or automated reports. In CTEs, SELECT * from a named CTE into a final outer SELECT is acceptable if the final SELECT explicitly names what it needs. Rule of thumb: if the query will run more than once, name the columns.

WHERE filters rows before grouping. HAVING filters groups after grouping. They operate at different stages of query execution. WHERE runs first — it restricts which rows are included in the aggregation. HAVING runs after GROUP BY — it restricts which groups appear in the final result based on aggregated values.

WHERE can only reference columns that exist in the raw table (individual row values). It cannot reference aggregate functions (SUM, COUNT, AVG) because the aggregation has not happened yet. HAVING can reference both the GROUP BY columns and aggregate functions applied to the group.

Performance implication: filter as early as possible using WHERE. If you can eliminate rows before grouping, the aggregation processes fewer rows and runs faster. HAVING should only contain conditions that genuinely require the aggregated value — anything else belongs in WHERE. Example: WHERE order_status = 'Delivered' eliminates non-delivered rows before counting, which is far more efficient than HAVING order_status = 'Delivered' which would group all rows first, then discard non-delivered groups.

CTEs (WITH clauses) are best for: breaking a complex query into readable named steps, referencing the same intermediate result multiple times within one query, and recursive queries. Most databases optimize CTEs inline (treating them as subqueries), so there is typically no performance difference between a CTE and an equivalent subquery — choose CTEs for readability. Use meaningful CTE names that describe what the set represents.

Subqueries are best for: simple one-off filtering (EXISTS / IN subqueries), scalar subqueries that return a single value, and cases where the subquery is used in only one place and is simple enough to read inline. Correlated subqueries run once per outer row — avoid them in SELECT lists when the same result can be achieved with a JOIN (the N+1 problem).

Temporary tables are best for: large intermediate results that need to be queried multiple times (CTEs re-materialize each time in some databases), results that need indexes for subsequent queries (you can CREATE INDEX on a temp table), and multi-step data transformation pipelines where each step produces a significant intermediate dataset. Temp tables have setup overhead; for small datasets, a CTE is faster and requires no cleanup.

The N+1 problem occurs when application code executes 1 query to get N rows, then executes 1 additional query per row to fetch related data — N+1 queries total. Example: fetch 100 orders (1 query), then for each order, fetch the customer name (100 queries) = 101 queries. At 100 orders this is annoying; at 10,000 orders it is catastrophic.

In raw SQL, the N+1 pattern appears as a correlated subquery in the SELECT list: SELECT order_id, (SELECT first_name FROM customers WHERE customer_id = o.customer_id) FROM orders o. This executes one subquery per order row.

Fix: JOIN the related table in the original query. SELECT o.order_id, c.first_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id. The database executes one query with one pass over both tables — O(1) queries regardless of N. In ORM frameworks (Django, Rails, SQLAlchemy), the equivalent fix is eager loading: select_related() in Django, includes() in Rails, joinedload() in SQLAlchemy. The fix is always the same: fetch all needed data in one query, not one query per row.

Rewrite the following poorly-written query using all best practices from this module: SELECT *, (SELECT COUNT(*) FROM order_items WHERE order_items.order_id = orders.order_id) as item_count FROM orders WHERE strftime('%Y', order_date) = '2024' AND order_status = 'Delivered'. Fix: (1) remove SELECT *, specify needed columns; (2) fix the non-SARGable date filter; (3) replace the correlated COUNT(*) subquery with a JOIN + GROUP BY; (4) use proper aliases and formatting.