SQL Clauses

Both clauses filter rows, but they operate at different stages of query execution.

WHERE filters individual rows before grouping. It applies to the raw rows in the table and cannot use aggregate functions.

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE salary > 50000       -- filter rows BEFORE grouping
GROUP BY department;

HAVING filters groups after GROUP BY. It can reference aggregate functions.

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE salary > 50000       -- filter rows before grouping
GROUP BY department
HAVING COUNT(*) > 5;       -- filter GROUPS after aggregation

Why the distinction matters — logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

HAVING executes after GROUP BY, so aggregate values exist at that point. WHERE executes before GROUP BY, so aggregates don't exist yet.

Common interview mistake: Writing WHERE COUNT(*) > 5 — this is invalid because COUNT(*) is not available at the WHERE stage. Use HAVING COUNT(*) > 5.

Can HAVING be used without GROUP BY? Yes — it applies to the entire result set as a single group:

SELECT AVG(salary) FROM employees HAVING AVG(salary) > 75000;

Performance tip: Filter as much as possible in WHERE before grouping, since reducing row count before GROUP BY is cheaper than filtering large groups in HAVING.

GROUP BY collapses multiple rows sharing the same value(s) in the specified column(s) into a single summary row. It is almost always used with aggregate functions.

Basic usage:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Each unique department value becomes one row in the result.

Multiple columns:

SELECT department, job_title, COUNT(*) AS count
FROM employees
GROUP BY department, job_title;

Groups by the combination of both columns.

With HAVING:

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) >= 10;
-- Only departments with 10 or more employees

Aggregate functions used with GROUP BY:

• COUNT(*) — total rows in each group
• SUM(col) — total of a numeric column per group
• AVG(col) — average per group
• MIN(col) / MAX(col) — extremes per group
• STRING_AGG(col, ',') / GROUP_CONCAT(col) — concatenate values per group

Important rule: Every column in SELECT must either be in the GROUP BY clause or be inside an aggregate function. Selecting a non-grouped, non-aggregated column is an error in standard SQL and PostgreSQL (MySQL/SQLite allow it but return an arbitrary value — a common source of bugs).

-- ERROR in PostgreSQL:
SELECT department, employee_name, COUNT(*) FROM employees GROUP BY department;
-- employee_name is neither in GROUP BY nor aggregated

Interview follow-up: What is the difference between COUNT(*) and COUNT(column)? COUNT(*) counts all rows; COUNT(column) counts only rows where the column is NOT NULL.

ORDER BY sorts the final result set of a query in ascending or descending order based on one or more columns.

Syntax:

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Key behaviors:

Default sort direction is ascending (ASC):

SELECT first_name, salary FROM employees ORDER BY salary;          -- ASC (smallest first)
SELECT first_name, salary FROM employees ORDER BY salary DESC;     -- largest first

Multiple sort columns (tiebreaker):

SELECT last_name, first_name, hire_date
FROM employees
ORDER BY last_name ASC, first_name ASC, hire_date DESC;

Sort by column position (fragile, avoid in production):

SELECT department, COUNT(*) FROM employees GROUP BY department ORDER BY 2 DESC;
-- ORDER BY 2 = second column = COUNT(*)

Sort using a SELECT alias (works because ORDER BY executes after SELECT):

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;

NULL sorting:

• In PostgreSQL and SQL Server: NULLs sort last in ASC, first in DESC
• PostgreSQL allows explicit control: ORDER BY column NULLS FIRST / NULLS LAST
• MySQL: NULLs sort first in ASC

Performance note: ORDER BY on large result sets without an index requires a sort operation (filesort in MySQL). If you always query in a specific order, an index on that column can eliminate the sort entirely.

Without LIMIT: ORDER BY alone does not limit rows — it only affects their sequence.

DISTINCT eliminates duplicate rows from a query's result set, returning only unique combinations of the selected columns.

Basic usage:

-- Without DISTINCT: may return duplicate department values
SELECT department FROM employees;

-- With DISTINCT: each department appears once
SELECT DISTINCT department FROM employees;

Multi-column DISTINCT — uniqueness is evaluated across all selected columns together:

SELECT DISTINCT department, job_title FROM employees;
-- Returns each unique (department, job_title) combination

Common use cases:

Find unique values in a column:

SELECT DISTINCT country FROM customers ORDER BY country;

Count unique values (COUNT DISTINCT):

SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

Remove duplicates from a UNION result — UNION already does this; UNION ALL with DISTINCT is redundant.

Performance implications:

• DISTINCT requires sorting or hashing the result to identify duplicates — this adds overhead
• On large result sets, DISTINCT can be expensive
• If you find yourself using DISTINCT to remove duplicates from a JOIN result, it may indicate a schema issue or a JOIN that produces unintended Cartesian pairs

DISTINCT vs GROUP BY: Both can produce unique values, but GROUP BY is used when you also need aggregates. For simply deduplicating output, DISTINCT is clearer. Some query planners generate identical execution plans for both.

Interview note: If an interviewer asks "when would you NOT use DISTINCT?" — answer: when you need duplicates to count correctly (e.g., a sales total should include each sale, not just unique sales).

A JOIN clause combines rows from two or more tables based on a related column between them, allowing you to retrieve data spread across multiple tables in a single query.

Basic syntax:

SELECT a.column1, b.column2
FROM table_a a
JOIN table_b b ON a.key_column = b.key_column;

Types of JOINs:

INNER JOIN (default) — returns rows where the join condition is met in both tables:

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
-- Only employees who have a matching department

LEFT (OUTER) JOIN — all rows from the left table + matching rows from the right (NULLs for no match):

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- All customers, even those with no orders

RIGHT (OUTER) JOIN — all rows from the right table + matching from the left (less common; rewrite as LEFT JOIN for clarity)

FULL (OUTER) JOIN — all rows from both tables, NULLs where there is no match

CROSS JOIN — Cartesian product of both tables (every row of A × every row of B)

The ON clause vs USING:

-- ON: explicit column names (flexible)
JOIN departments d ON e.department_id = d.id

-- USING: when column names are identical in both tables
JOIN departments d USING (department_id)

Interview follow-up: What is an anti-join? A LEFT JOIN ... WHERE right_table.key IS NULL — finds rows in the left table with no match in the right table.