Using Alias in WHERE Clause

SELECT salary * 12 AS annual_salary FROM employees WHERE annual_salary > 100000;

-- Option 1: repeat the expression

SELECT salary * 12 AS annual_salary FROM employees WHERE salary * 12 > 100000;

-- Option 2: CTE (most readable)

WITH emp AS (SELECT salary * 12 AS annual_salary FROM employees)

SELECT * FROM emp WHERE annual_salary > 100000;

SQL execution order: FROM → WHERE → SELECT. WHERE runs before SELECT, so the alias annual_salary has not been defined yet when WHERE is evaluated. Fix: repeat the expression in WHERE, or wrap in a subquery/CTE.

High

NULL Propagation in String Concatenation

SELECT first_name || ' ' || last_name AS full_name FROM employees;

SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name FROM employees;

PostgreSQL and Oracle's || operator returns NULL if any operand is NULL. If either first_name or last_name is NULL, full_name becomes NULL. COALESCE wraps each nullable column to substitute an empty string.

High

Non-SARGable Function on Indexed Column

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

YEAR() wraps the indexed order_date column, preventing index use and causing a full table scan. The SARGable rewrite uses a range condition directly on the indexed column, enabling an index seek. Can be 100x faster on large tables.

Critical

Missing Parentheses in Multi-Step Formula

SELECT quantity * unit_price * 1 - discount_pct AS total FROM order_items;

SELECT (quantity * unit_price) * (1 - discount_pct) AS total FROM order_items;

Without parentheses, operator precedence makes this: (quantity * unit_price * 1) - discount_pct, subtracting a small decimal (e.g., 0.10) instead of applying a 10% discount. Parentheses enforce the intended calculation order.

Critical

SQL Server + Operator with NULL

SELECT first_name + ' ' + last_name AS full_name FROM employees; -- SQL Server

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; -- SQL Server

SQL Server's + string operator propagates NULL — if either name is NULL the result is NULL. CONCAT() in SQL Server 2012+ treats NULL as empty string. Use CONCAT() for NULL-safe portable concatenation.

Medium

Arithmetic on Indexed Column Breaks SARGability

SELECT * FROM employees WHERE salary * 12 > 120000;

SELECT * FROM employees WHERE salary > 10000;

Applying arithmetic to an indexed column (salary * 12) forces the database to evaluate the expression for every row. Moving the math to the constant side (salary > 10000) leaves the indexed column unchanged, enabling index scan.

High