Question 1

What is a subquery in SQL?

Accepted Answer

A subquery is a query nested inside another SQL query. It can appear in SELECT, FROM, WHERE, or HAVING clauses. The subquery executes first and its result is used by the outer query. This mechanism allows breaking down complex problems into successive logical steps.

Question 2

In which SQL clause can a scalar subquery be used?

Accepted Answer

A scalar subquery returns exactly one value (one row, one column). Thanks to this property, it can be used in the SELECT clause as a computed column. It can also appear in WHERE for direct comparisons. Subqueries returning multiple rows cannot be used where a single value is expected.

Question 3

What is the difference between a subquery and a join?

Accepted Answer

A subquery is an independent nested query that produces an intermediate result used by the outer query, while a join directly combines columns from multiple tables into a single result set. Joins are generally more performant for combining data, but subqueries are sometimes more readable for complex filters or intermediate calculations.

Question 4

How to use the IN keyword with a subquery to filter results?

Accepted Answer

The IN keyword checks whether a value belongs to a set returned by a subquery. The subquery must return a single column containing the comparison values. It is an alternative to multiple OR conditions and is particularly useful when the list of values comes from another table or a dynamic calculation.

Question 5

What is a correlated subquery?

Accepted Answer

A correlated subquery references one or more columns from the outer query. It is re-evaluated for each row processed by the main query, which distinguishes it from a simple subquery executed only once. This mechanism enables row-by-row comparisons but can be costly in performance on large data volumes.

Question 6

What is the role of the EXISTS keyword in a subquery?

Accepted Answer

EXISTS returns TRUE if the associated subquery returns at least one row, regardless of the values returned. The SQL engine stops evaluation as soon as a matching row is found, making it often more performant than IN for large tables. It is commonly used with correlated subqueries to check for the existence of related records.

Question 7

What is the difference between EXISTS and IN when using subqueries?

Accepted Answer

EXISTS evaluates the presence of at least one row in the subquery and stops at the first match found, making it efficient on large tables. IN compares a value against the complete set of subquery results. EXISTS handles NULL values correctly unlike IN which can produce unexpected results with NOT IN when the subquery contains NULLs.

Question 8

How to use NOT EXISTS to find records with no match in another table?

Accepted Answer

NOT EXISTS returns TRUE when the correlated subquery returns no rows. It is the recommended method for finding orphan records because it correctly handles NULL values, unlike NOT IN. The correlated subquery typically compares the primary key of the outer table with the foreign key of the inner table.

Question 9

What is a subquery in the FROM clause (derived table)?

Accepted Answer

A subquery in the FROM clause creates a derived table that acts as a temporary virtual table. It must be given an alias to be referenced in the outer query. This technique is useful for pre-aggregating data or applying intermediate transformations before joining or filtering them in the main query.

Question 10

What is a CTE (Common Table Expression) in SQL?

Accepted Answer

A CTE is a named temporary result set defined with the WITH keyword that exists only for the duration of the query in which it is declared. It improves readability by allowing complex queries to be broken down into named logical blocks. Unlike temporary tables, a CTE is not physically stored and does not persist beyond the query.

Question 11

What is the correct syntax to define a CTE in SQL?

Accepted Answer

The CTE syntax starts with the WITH keyword, followed by the CTE name, the AS keyword, then the query in parentheses. The main query using the CTE follows immediately after. It is possible to define multiple CTEs by separating them with commas after a single WITH, allowing successive transformations to be chained.

Question 12

What main advantage does a CTE offer over a nested subquery?

Accepted Answer

The main advantage of a CTE is improving readability and maintainability of SQL code. By naming each logical step, the CTE makes the data transformation flow explicit and easy to understand. Additionally, a CTE can be referenced multiple times in the same query, avoiding code duplication unlike subqueries which must be rewritten for each use.

Question 13

How to define multiple CTEs in the same query?

Accepted Answer

To define multiple CTEs, use a single WITH keyword at the beginning, then separate each CTE with a comma. Each subsequent CTE can reference CTEs defined before it, allowing step-by-step data transformation pipelines to be built. The final main query can then use any of the defined CTEs.

Question 14

When to use a correlated subquery with EXISTS rather than a join?

Accepted Answer

EXISTS with a correlated subquery is particularly suited when checking for the existence of related records without needing columns from the associated table in the result. Unlike a join which can duplicate rows in one-to-many relationships, EXISTS returns each row from the main table at most once, avoiding unwanted duplicates.

Question 15

What is a recursive CTE and what is it used for?

Accepted Answer

A recursive CTE is a CTE that references itself in its definition. It consists of an anchor member (base case) that provides the starting point, combined with UNION ALL to a recursive member that references the CTE. It is ideal for traversing hierarchical structures such as org charts, nested categories, or parent-child relationship graphs.

Question 16

How to prevent an infinite loop in a recursive CTE?

Accepted Answer

To prevent an infinite loop, it is essential to include a termination condition in the WHERE clause of the recursive member that will eventually return no rows. Additionally, most databases offer a MAXRECURSION option (SQL Server) or similar parameter to limit the number of iterations. BigQuery defaults to a limit of 500 recursive iterations.

Question 17

How to refactor a query with multiple nested subqueries using CTEs?

Accepted Answer

Refactoring involves extracting each nested subquery into a distinct named CTE, starting with the deepest subquery. Each CTE represents a logical step in data processing. Subsequent CTEs can reference previous ones, creating a readable top-to-bottom transformation pipeline. The final query contains only references to the named CTEs.

Question 18

What problem can NOT IN cause with a subquery containing NULL values?

Accepted Answer

When a subquery used with NOT IN contains NULL values, the result is always an empty set. This comes from SQL's three-valued logic: any comparison with NULL returns UNKNOWN, and NOT IN requires all comparisons to be FALSE to return TRUE. A single NULL value is enough to make the result indeterminate. NOT EXISTS does not suffer from this problem.

Question 19

How to use a CTE to calculate a global total and display it on each row of a detailed result?

Accepted Answer

A scalar CTE can calculate a global total (for example total sales), then be joined with CROSS JOIN to the detail table to add that value to each row. This allows calculating percentages of total or deviations from the mean without a repeated subquery in SELECT. This approach is more readable than a scalar subquery in the SELECT clause.

Question 20

In what execution order does the SQL engine process CTEs relative to the main query?

Accepted Answer

CTEs are logically evaluated before the main query, in the order they are declared. However, the query optimizer may choose to materialize them (calculate once and store) or inline them (integrate into the main query as a subquery). The behavior depends on the SQL engine: PostgreSQL materializes CTEs by default, while BigQuery and MySQL inline them to optimize execution.

SQL - Subqueries and CTEs

What is a subquery in SQL?

Answer

In which SQL clause can a scalar subquery be used?

Answer

What is the difference between a subquery and a join?

Answer

How to use the IN keyword with a subquery to filter results?

What is a correlated subquery?

What is the role of the EXISTS keyword in a subquery?

Master Data Analytics for your next interview