Interview Questions
A subquery is a query nested inside another query. Its basic purpose is to return data that will be used in the main query as a condition or as a derived table. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses.
A correlated subquery references columns from the outer query and is executed once for each row processed by the outer query. A non-correlated subquery is independent of the outer query and executes once for the entire query.
A derived table is a subquery in the FROM clause that acts as a temporary table for the duration of the query. It must have an alias and can be used like a regular table in the main query.
Scalar subqueries are subqueries that return exactly one row and one column. They're used when a single value is needed, such as in comparisons or calculations, and can appear in SELECT, WHERE, or HAVING clauses.
EXISTS checks whether a subquery returns any rows. It's often used with correlated subqueries to test for the existence of related records, returning TRUE if the subquery returns any rows and FALSE if it doesn't.
IN compares a value against a list of values returned by the subquery, while EXISTS checks for the presence of any rows. EXISTS often performs better with large datasets as it stops processing once a match is found.
Subqueries in the SELECT clause must return a single value per row of the outer query. They're often used to calculate values or retrieve related data from other tables for each row in the result set.
Correlated subqueries can impact performance as they execute once for each row in the outer query. This can be inefficient for large datasets and might be better replaced with JOINs or other query constructs.
NULL values require special handling in subqueries, especially with NOT IN operations. It's important to either filter out NULLs or use NOT EXISTS instead of NOT IN to avoid unexpected results due to NULL comparison behavior.
A CTE is a named temporary result set that exists within the scope of a single statement. Unlike subqueries, CTEs can be referenced multiple times within a query and can be recursive. They often improve readability and maintenance.
Tables can be updated using subqueries in the SET clause or WHERE clause. The subquery can provide values for the update or identify which rows to update. Care must be taken with correlated subqueries to avoid updating the same table being referenced.
A lateral join allows subqueries in the FROM clause to reference columns from preceding items in the FROM clause. This enables row-by-row processing with access to outer query columns, similar to correlated subqueries.
Subqueries can be used with aggregate functions to compare individual values against group results, such as finding rows where a value exceeds the average. They can appear in HAVING clauses or as scalar subqueries in the SELECT list.
Subqueries have limitations including: cannot contain ORDER BY (except in TOP/LIMIT clauses), cannot be used with UNION/INTERSECT/EXCEPT in certain contexts, and must return single values in scalar contexts. Performance can also be a limitation with complex nested queries.
Subqueries can be used in INSERT statements to populate new records based on existing data. They can provide values for specific columns or complete rows, and can be combined with SELECT statements to insert multiple rows.
ANY/SOME compares a value with each value returned by a subquery, returning TRUE if any comparison is true. It's often used with comparison operators like '>', '<', or '=' to find matches against multiple values.
The ALL operator compares a value with every value returned by a subquery, returning TRUE only if all comparisons are true. It's useful for finding values that satisfy conditions against an entire set of results.
A nested subquery is a subquery within another subquery. While they can solve complex problems, each level of nesting can impact performance and readability. They should be used judiciously and potentially refactored using JOINs or CTEs.
Subquery performance can be optimized by: using EXISTS instead of IN for large datasets, avoiding correlated subqueries when possible, using JOINs instead of subqueries where appropriate, and ensuring proper indexing on referenced columns.
A materialized subquery is one where the results are computed once and stored temporarily, rather than being recomputed for each row. This can improve performance for complex subqueries referenced multiple times in the main query.
Subqueries in DELETE statements can identify which records to remove based on complex conditions or relationships with other tables. Care must be taken with correlated subqueries to avoid affecting the subquery's results during deletion.
While both can relate data from multiple tables, subqueries create a nested query structure while joins combine tables horizontally. Joins often perform better but subqueries can be more readable for certain operations like existence checks.
Subqueries can contain window functions to perform calculations before the results are used in the main query. This is often done in derived tables or CTEs where the window function results need further processing.
A recursive subquery is used in a CTE to query hierarchical or graph-like data structures. It combines a base case with a recursive part to traverse relationships like organizational charts or bill of materials.
Error handling in subqueries involves checking for NULL results, handling no-data scenarios, ensuring single-row returns for scalar subqueries, and using CASE expressions or COALESCE to handle exceptional cases.
Subqueries operate within the same transaction as the main query, but complex subqueries can affect lock duration and concurrency. Correlated subqueries may hold locks longer due to row-by-row processing.
Subqueries in dynamic SQL must be properly formatted and escaped. They can be used to create flexible queries based on runtime conditions, but care must be taken to prevent SQL injection and ensure proper parameter handling.
Inline views are subqueries in the FROM clause that create temporary result sets. They're useful for breaking down complex queries, pre-aggregating data, or applying transformations before joining with other tables.
Subqueries in CASE expressions must return scalar values and can be used to create conditional logic based on queries against other tables or aggregated data. They're useful for complex categorical assignments or calculations.