Unit

Advanced SELECT Statements

Learn advanced SQL SELECT queries for filtering, computed values, aggregation, grouping, joins, subqueries, set operations, CTEs, and window functions.

An SQL SELECT statement retrieves data from one or more tables or query results. The returned rows and columns are called the result set. Advanced SELECT statements let you filter precisely, calculate values, summarize records, combine tables, and analyze related rows.

This lesson assumes that you know basic SELECT, FROM, WHERE, and ORDER BY syntax. For a syntax refresher, see SQL Commands Syntax.

Advanced query structure

A query is written in a familiar order, but SQL conceptually processes its clauses in a different order:

1. FROM identifies source tables and joins them.
2. WHERE removes source rows that do not satisfy a predicate.
3. GROUP BY forms groups when aggregation is requested.
4. HAVING removes completed groups.
5. SELECT calculates and chooses output columns.
6. ORDER BY sorts the final rows.
7. LIMIT, TOP, or FETCH FIRST restricts the final result.

This is a conceptual processing order, not a promise about the database engine's physical execution plan. Optimizers may rearrange operations while preserving the query's meaning.

SELECT c.city AS customer_city,
       COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.signup_date >= '2025-01-01'
GROUP BY c.city
HAVING COUNT(o.order_id) > 5
ORDER BY order_count DESC
FETCH FIRST 10 ROWS ONLY;

In this example, the join creates a combined source, WHERE filters customers, GROUP BY creates city groups, HAVING filters those groups, and ORDER BY sorts the final summaries.

Aliases

An alias is a temporary name assigned to a table or output column. Table aliases make joins shorter and clarify which table owns a column. Column aliases make calculated output understandable.

SELECT p.product_name AS name,
       p.unit_price * 1.10 AS price_with_tax
FROM products AS p;

Use qualified names such as p.product_name whenever multiple tables contain similarly named columns. In many databases, a column alias can be used in ORDER BY, but not reliably in WHERE, because WHERE is conceptually evaluated earlier.

Filtering with advanced conditions

A predicate is a condition that determines whether a row qualifies. Combine predicates with Boolean operators, and use parentheses whenever the intended grouping is not obvious.

SELECT product_id, product_name, unit_price
FROM products
WHERE active = TRUE
  AND (category_id IN (2, 4, 7)
       OR unit_price BETWEEN 10 AND 25)
  AND product_name LIKE 'Pro%';

• AND requires both conditions to be true.
• OR requires at least one condition to be true.
• NOT reverses a condition, such as NOT (status = 'Cancelled').
• Parentheses control evaluation and prevent an AND condition from being combined with an unintended OR branch.

Membership, ranges, and patterns

SELECT *
FROM orders
WHERE status IN ('Paid', 'Shipped')
  AND order_date BETWEEN '2025-01-01' AND '2025-03-31';

IN tests membership in a list or subquery. NOT IN tests that a value is not in the set, but it has important NULL behavior discussed below. BETWEEN is inclusive at both ends, so the example includes January 1 and March 31.

LIKE performs pattern matching. The percent wildcard % represents zero or more characters, and the underscore wildcard _ represents exactly one character.

SELECT product_name
FROM products
WHERE product_name LIKE 'Net%'
   OR product_name LIKE '__-Pro';

Pattern matching and case sensitivity vary by database collation and dialect. Use the platform's case-insensitive matching feature when required.

NULL and three-valued logic

NULL marks missing or unknown data. It is not zero, an empty string, or a regular value. Do not write email = NULL; that comparison evaluates to unknown rather than true.

SELECT customer_id, customer_name
FROM customers
WHERE email IS NULL;

SELECT customer_id, customer_name
FROM customers
WHERE email IS NOT NULL;

SQL predicates can evaluate to true, false, or unknown. A WHERE clause keeps only rows for which the predicate is true. Comparisons such as salary > 50000 are unknown when salary is NULL. Boolean expressions involving unknown values can also become unknown.

Be especially careful with NOT IN. If its list or subquery contains NULL, the result may be unknown for every candidate value. Exclude NULLs in the subquery or use NOT EXISTS for anti-match logic.

Sorting and limiting result sets

ORDER BY sorts the result set. Ascending order is the default; use DESC for descending order. Multiple expressions are applied from left to right.

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, order_id DESC;

The second sort key breaks ties from the first. This is important when selecting only the first few rows.

Syntax varies by database. A row limit without a complete, deterministic ORDER BY does not define which tied rows are returned. Include a unique tie-breaker, such as order_id, when consistent results matter.

Computed columns and conditional expressions

A row-level expression calculates a value independently for each source row. Expressions can use arithmetic, text operations, dates, and conditional logic.

SELECT product_name,
       unit_price,
       unit_price * 0.90 AS sale_price,
       CASE
         WHEN unit_price < 20 THEN 'Budget'
         WHEN unit_price < 100 THEN 'Standard'
         ELSE 'Premium'
       END AS price_category
FROM products;

CASE returns the result from the first matching branch. An ELSE branch is recommended so that unexpected values do not silently produce NULL.

SELECT customer_name,
       COALESCE(email, 'No email supplied') AS contact_email
FROM customers;

COALESCE returns the first non-NULL expression. Equivalent NULL-handling functions differ among SQL dialects.

A row-level expression preserves one output row per input row. An aggregate expression, such as SUM(total_amount), combines multiple rows into one value for a whole result or group.

Aggregate queries

An aggregate function summarizes multiple rows. Common functions are:

• COUNT counts rows or non-NULL values.
• SUM adds numeric values.
• AVG calculates an average of non-NULL numeric values.
• MIN and MAX return the lowest and highest non-NULL values.

SELECT COUNT(*) AS all_orders,
       COUNT(total_amount) AS orders_with_amount,
       SUM(total_amount) AS revenue,
       AVG(total_amount) AS average_order,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders
WHERE status = 'Paid';

COUNT(*) counts qualifying rows, including rows where individual columns are NULL. COUNT(total_amount) counts only rows whose total_amount is not NULL. Most aggregates ignore NULL values. If every input value is NULL, functions such as SUM and AVG commonly return NULL rather than zero.

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

DISTINCT inside an aggregate removes duplicate input values before calculation. Use it only when the business question requires unique values.

Grouping and group filtering

GROUP BY forms groups that share the same grouping values. Every selected expression must either define a grouping value or be an aggregate expression, subject to database-specific extensions.

SELECT d.department_name,
       COUNT(e.employee_id) AS employee_count,
       AVG(e.salary) AS average_salary,
       SUM(e.salary) AS payroll
FROM departments AS d
JOIN employees AS e
  ON e.department_id = d.department_id
WHERE e.salary IS NOT NULL
GROUP BY d.department_name
HAVING SUM(e.salary) > 250000
ORDER BY payroll DESC;

WHERE filters individual source rows before grouping. HAVING filters completed groups and is appropriate for conditions involving aggregates.

SELECT department_id, status, COUNT(*) AS order_count
FROM orders
GROUP BY department_id, status;

Grouping by multiple columns creates one group for each distinct combination. A common error is selecting a non-grouped, non-aggregated column:

-- Invalid in many SQL databases:
SELECT department_id, employee_name, AVG(salary)
FROM employees
GROUP BY department_id;

The query does not specify which employee name represents a department. Add employee_name to GROUP BY if each employee should be a separate group, or remove it and return a department-level summary.

Joins across related tables

A join combines related rows from separate tables. A primary key uniquely identifies a row; a foreign key stores a value that refers to a related primary key. The relationship between customers.customer_id and orders.customer_id is a typical join relationship.

INNER JOIN

An INNER JOIN returns only rows with a match on both sides.

SELECT c.customer_name,
       o.order_id,
       o.order_date,
       o.total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON o.customer_id = c.customer_id;

This report excludes customers who have never placed an order.

LEFT JOIN

A LEFT JOIN, also called a left outer join, preserves every row from the left table. Columns from the right table become NULL when no match exists.

SELECT c.customer_id,
       c.customer_name,
       o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id;

SELECT c.customer_id,
       c.customer_name,
       COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

The second query includes customers with zero orders because COUNT(o.order_id) counts a right-side key and therefore counts zero for an unmatched row. COUNT(*) would count the preserved customer row instead.

Other join types and self joins

A RIGHT JOIN preserves all rows from the right input and can usually be rewritten as a LEFT JOIN by reversing table order. A FULL OUTER JOIN preserves unmatched rows from both inputs. Availability and syntax vary by database.

A self join joins a table to itself. Use different aliases to represent the two roles:

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON m.employee_id = e.manager_id;

Always provide a correct ON condition. Without one, the database may create a Cartesian product: every row from one input is paired with every row from the other. An incomplete condition or a join on a non-unique value can also multiply rows. Qualify ambiguous columns and inspect the relationship keys.

Join troubleshooting

A LEFT JOIN can accidentally behave like an INNER JOIN when a right-table condition is placed in WHERE:

-- Removes customers without a matching paid order:
SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'Paid';

To preserve customers without orders, put the matching condition in the join:

SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'Paid';

When a join returns too many rows, compare row counts before and after each join, inspect whether the join keys are unique, and decide whether the one-to-many rows are meaningful. Use aggregation or a corrected join condition rather than adding DISTINCT automatically.

Subqueries

A subquery is a query nested inside another statement. Its form should match the number of values it returns.

Scalar subqueries

A scalar subquery returns one value, such as a single average:

SELECT employee_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

The inner query must return one value. A scalar subquery that returns multiple rows usually causes an error.

Multi-row subqueries and EXISTS

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'Paid'
);

IN compares a value with the values returned by a subquery. EXISTS asks only whether at least one related row exists:

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'Paid'
);

EXISTS is conceptually a yes-or-no test and can avoid the NULL concerns associated with NOT IN. Depending on the optimizer and data, a join may be clearer or more efficient. Other comparison forms include ANY and ALL where supported; for example, salary > ALL (subquery) means the salary exceeds every value returned.

Correlated subqueries

A correlated subquery refers to a value from the current outer-query row. It is evaluated in relation to that row.

SELECT e.employee_name,
       e.department_id,
       e.salary
FROM employees AS e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees AS e2
  WHERE e2.department_id = e.department_id
);

This returns employees paid above the average salary in their own department. A correlated subquery can be useful, but a join or window function may communicate the same logic more clearly for complex or large datasets.

Subquery contexts

• WHERE: filter rows using a scalar, multi-row, or existence test.
• HAVING: compare a completed group with another result.
• FROM: use a derived table, which is a subquery treated as a temporary table.
• SELECT: calculate a scalar value for each output row.

Set operations

Set operations combine compatible result sets. Each query must return the same number of columns in the same order, with compatible data types.

SELECT order_id, order_date, total_amount
FROM orders
WHERE status = 'Paid'
UNION ALL
SELECT order_id, order_date, total_amount
FROM archived_orders
WHERE status = 'Paid';

UNION ALL preserves every row. UNION removes duplicate complete rows, which requires additional work and can produce fewer rows than expected.

INTERSECT returns rows present in both results. EXCEPT returns rows in the first result but not the second. Some databases use dialect-specific equivalents, and support varies.

SELECT customer_id FROM orders
UNION
SELECT customer_id FROM support_requests
ORDER BY customer_id;

Place ORDER BY on the final combined result, not on each individual query unless the dialect and use case specifically allow it.

DISTINCT and duplicate management

SELECT DISTINCT returns unique combinations of all selected columns:

SELECT DISTINCT city, signup_date
FROM customers;

Distinctness applies to the entire selected row. Two rows with the same city but different signup dates are not duplicates in this result.

Duplicates after a join may be legitimate. For example, one customer can have many orders, and one order can have many order items. Diagnose the relationship before choosing a solution:

• Use DISTINCT when the required output is genuinely a set of unique combinations.
• Use aggregation when several detail rows must become a summary, such as one row per customer.
• Correct the join condition when rows are duplicated because the relationship was joined incorrectly.

Derived tables and common table expressions

A derived table is a subquery in the FROM clause. It stages an intermediate result that the outer query can query.

SELECT customer_id, total_spent
FROM (
  SELECT customer_id,
         SUM(total_amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 1000
ORDER BY total_spent DESC;

A common table expression, or CTE, is a named temporary result defined with WITH. CTEs make multi-step queries easier to read and test.

WITH customer_totals AS (
  SELECT customer_id,
         SUM(total_amount) AS total_spent
  FROM orders
  GROUP BY customer_id
), qualified_customers AS (
  SELECT customer_id, total_spent
  FROM customer_totals
  WHERE total_spent > 1000
)
SELECT c.customer_name, q.total_spent
FROM qualified_customers AS q
JOIN customers AS c
  ON c.customer_id = q.customer_id
ORDER BY q.total_spent DESC;

Some databases support recursive CTEs for hierarchical data such as an employee-management tree. Treat recursion as an optional advanced feature and verify the dialect's syntax and termination rules.

Window functions

A window function calculates across related rows while retaining individual result rows. This differs from GROUP BY, which normally collapses each group into one output row.

OVER defines the window. PARTITION BY divides rows into independent groups for the calculation, and the window's ORDER BY defines sequence within each partition.

SELECT product_id,
       product_name,
       category_id,
       revenue,
       ROW_NUMBER() OVER (
         PARTITION BY category_id
         ORDER BY revenue DESC, product_id
       ) AS row_number_in_category,
       RANK() OVER (
         PARTITION BY category_id
         ORDER BY revenue DESC
       ) AS revenue_rank,
       DENSE_RANK() OVER (
         PARTITION BY category_id
         ORDER BY revenue DESC
       ) AS dense_revenue_rank
FROM product_revenue;

• ROW_NUMBER assigns a unique sequence, even when values tie.
• RANK gives tied rows the same rank and leaves gaps after ties.
• DENSE_RANK gives tied rows the same rank without gaps.

A running total can be calculated with an ordered window:

SELECT order_date,
       total_amount,
       SUM(total_amount) OVER (
         ORDER BY order_date, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue
FROM orders;

To return the top products in each category, first calculate product revenue, then rank it in a CTE:

WITH product_revenue AS (
  SELECT p.product_id,
         p.product_name,
         p.category_id,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM products AS p
  JOIN order_items AS oi
    ON oi.product_id = p.product_id
  GROUP BY p.product_id, p.product_name, p.category_id
), ranked_products AS (
  SELECT product_revenue.*,
         ROW_NUMBER() OVER (
           PARTITION BY category_id
           ORDER BY revenue DESC, product_id
         ) AS position_in_category
  FROM product_revenue
)
SELECT product_id, product_name, category_id, revenue
FROM ranked_products
WHERE position_in_category <= 3
ORDER BY category_id, position_in_category;

Complete practical examples

Filter products by multiple criteria

SELECT p.product_id,
       p.product_name,
       p.unit_price,
       COALESCE(p.category_id, 0) AS category_id,
       CASE WHEN p.active = TRUE THEN 'Available'
            ELSE 'Unavailable'
       END AS availability
FROM products AS p
WHERE p.active = TRUE
  AND p.unit_price BETWEEN 10 AND 100
  AND p.category_id IN (2, 4, 7)
  AND (p.product_name LIKE 'Pro%'
       OR p.category_id IS NULL)
ORDER BY p.unit_price DESC, p.product_id;

The parentheses make the product-name and missing-category alternatives one logical unit. Notice that the earlier category_id IN (...) condition conflicts with category_id IS NULL; revise the business rule if NULL categories are intended to qualify:

WHERE p.active = TRUE
  AND p.unit_price BETWEEN 10 AND 100
  AND (p.category_id IN (2, 4, 7)
       OR p.category_id IS NULL)
  AND p.product_name LIKE 'Pro%';

Sales summary by department

SELECT d.department_name,
       COUNT(*) AS sale_count,
       SUM(o.total_amount) AS total_sales,
       AVG(o.total_amount) AS average_sale
FROM departments AS d
JOIN employees AS e
  ON e.department_id = d.department_id
JOIN orders AS o
  ON o.sales_employee_id = e.employee_id
WHERE o.status = 'Paid'
GROUP BY d.department_id, d.department_name
HAVING SUM(o.total_amount) > 50000
ORDER BY total_sales DESC, d.department_name;

Grouping by both department ID and name avoids relying on a name's uniqueness. If the schema does not have orders.sales_employee_id, use the actual relationship available in your database.

Query quality, performance, and safety

• Format each major clause on its own line and indent joins and nested conditions.
• Use meaningful aliases such as customer_totals, order_count, and position_in_category.
• Build incrementally: start with FROM, add the join, inspect rows, then add filters, grouping, and calculations.
• Check row counts before and after joins. A sudden increase may indicate a one-to-many relationship or a faulty condition.
• Select only needed columns instead of using SELECT * in production reports or application queries.
• Use selective filters and appropriate indexes where supported, but inspect the database's execution plan before assuming a change improves performance.
• Avoid unnecessary nested queries and repeated correlated subqueries when a CTE, join, or window function expresses the operation more clearly.
• Use parameterized queries in application code. Do not construct SQL by concatenating user input.

-- Conceptual application pattern; placeholder syntax varies:
SELECT customer_id, customer_name
FROM customers
WHERE city = ?
  AND signup_date >= ?;

Parameterization separates SQL structure from values and helps prevent injection attacks. Date functions, Boolean literals, string concatenation, NULL functions, row limiting, and join support differ among SQL dialects. Always check the documentation for the database you are using.

Common errors and troubleshooting

Exam-relevant distinctions

• WHERE versus HAVING: WHERE filters source rows; HAVING filters groups after aggregation.
• INNER JOIN versus LEFT JOIN: an inner join keeps matches only; a left join keeps every left-side row.
• COUNT(*) versus COUNT(column): the former counts qualifying rows; the latter ignores NULL column values.
• GROUP BY versus window functions: grouping collapses rows into summaries; windows calculate across related rows while preserving detail rows.
• UNION versus UNION ALL: UNION removes duplicate complete rows; UNION ALL preserves them.
• IN versus EXISTS: IN compares values with a returned set; EXISTS tests whether at least one row is returned.
• DISTINCT: uniqueness applies to the complete selected combination, not to one column independently.
• NULL: use IS NULL, not = NULL; unknown values affect Boolean expressions.
• Top-N queries: apply a deterministic ORDER BY before limiting rows.

Summary

Advanced SELECT statements combine row filtering, expressions, aggregation, grouping, joins, nested queries, set operations, CTEs, and window functions. Write the simplest correct layer first, verify its rows, and then add the next operation. Understanding the conceptual processing order and the effect of NULLs and join cardinality will prevent many subtle errors.