SQL is not limited to reading individual rows. Aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() perform calculations across sets of rows, producing summary values like totals or averages. When these aggregates are combined with GROUP BY, the table is partitioned into groups that share the same values in the listed columns, and an aggregate is computed per group. For example, SELECT department, AVG(salary) FROM employees GROUP BY department; gives the average salary for each department. While WHERE filters individual rows before grouping, HAVING filters the resulting groups; HAVING AVG(salary) > 50000 keeps only departments whose average salary exceeds that threshold.
Most real-world queries combine data from multiple tables. Joins make this possible by combining rows from two or more tables based on related columns. An INNER JOIN returns only rows with matching values in both tables, while a LEFT JOIN returns all rows from the left table along with their matches from the right, filling non-matches with NULLs. The RIGHT JOIN does the mirror image, and a FULL OUTER JOIN returns all rows from both sides, again with NULLs where no match exists. A special case is the self-join, where a table is joined to itself, which is helpful for traversing hierarchical relationships such as an employee-to-manager chain.
Sometimes a query needs to draw on the results of another query. A subquery is a SELECT statement nested inside another query, used in the WHERE, FROM, or SELECT clauses, such as SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);. A correlated subquery goes further by referencing columns from the outer query, so it executes once per outer row; this is useful for checks like SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = u.id);, which finds users who have placed at least one order. Finally, UNION combines the result sets of two queries and removes duplicates, while UNION ALL keeps duplicates; both require the queries to return compatible columns in the same order.