subspørringer

Subspørringer are queries nested inside another SQL query. The inner query provides values or a result set that the outer query uses to complete its operation. They can appear in the SELECT clause as scalar values, in the FROM clause as derived tables, or in the WHERE clause to filter rows.

They serve several purposes, such as filtering by a value produced by another query, computing a value to be displayed, or supplying a temporary table that the outer query can join against. Subspørringer can be unCorrelated, meaning they do not reference columns from the outer query and can be executed once, or correlated, meaning they refer to columns from the outer query and may be evaluated for each row.

Two common forms are scalar and EXISTS subspørringer. A scalar subspørring returns a single value, for example: SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales'); An EXISTS subspørring tests for the existence of rows and is often used for performance-friendly filtering: SELECT e.name FROM employees e WHERE EXISTS (SELECT 1 FROM salaries s WHERE s.employee_id = e.id AND s.amount > 100000).

Subspørringer can also be used with IN, ANY, or ALL to compare against multiple values. In practice, they are powerful but may be less efficient than equivalent joins, especially when the subspørring is correlated. Depending on the database system and indexes, rewriting with joins or using common table expressions (CTEs) can improve performance and readability. Subspørringer are widely supported in modern relational databases and are a core tool for complex data retrieval.