SQL Server interview questions covering joins, subqueries, ctes & set operators.
Open a question for the full answer, code and interview guidance.
A JOIN combines rows from two or more data sources based on a relationship between them. Joins are fundamental in relational databases because normalized data is usually distributed across multiple related tables. The most common join types are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN. The join condition is normally written in the ON clause.
INNER JOIN returns only rows for which the join predicate finds a match on both sides. For example, if Products.CategoryId references Categories.Id, an INNER JOIN returns products that have matching category rows. Rows without a matching counterpart are excluded from the result.
LEFT JOIN, also called LEFT OUTER JOIN, returns every row from the left table and matching rows from the right table. When no right-side match exists, columns from the right side are returned as NULL. It is commonly used to find parent records even when optional related data does not exist.
RIGHT JOIN, or RIGHT OUTER JOIN, returns every row from the right table and matching rows from the left table. Unmatched left-side columns become NULL. Most RIGHT JOIN queries can be rewritten as LEFT JOIN by reversing table order. Many teams prefer LEFT JOIN for consistency and readability, but RIGHT JOIN is fully supported.
FULL OUTER JOIN returns matched rows plus unmatched rows from both sides. When a row has no match on the opposite side, columns from that side contain NULL. It is useful for reconciliation and comparison tasks, such as comparing records from two systems and identifying records that exist in only one source.
CROSS JOIN returns the Cartesian product of two data sources: every row from the first source is paired with every row from the second. If one table has 10 rows and another has 5 rows, the CROSS JOIN produces 50 rows. It is useful for generating combinations, but an accidental Cartesian product can create an extremely large result set.
A self join joins a table to itself using different aliases. It is commonly used for hierarchical or same-table relationships, such as employees and managers. A self join is not a separate SQL JOIN keyword; it is a normal join where both references point to the same table.
For an outer join, placing a predicate in ON can produce a different result from placing it in WHERE. The ON clause determines which rows match during the join while preserving required outer rows. A WHERE predicate is applied after the joined result is formed. Therefore, filtering a nullable right-side column in WHERE can eliminate unmatched rows and effectively turn a LEFT JOIN into an inner-like result for that condition. This distinction is a frequent interview and production-query issue.
A common pattern is LEFT JOIN followed by a NULL check on a non-nullable key from the right table. Another often clearer pattern is NOT EXISTS. For example, to find categories with no products, check that no related product exists. NOT EXISTS is generally robust and avoids some NULL-related problems associated with NOT IN.
A subquery is a query nested inside another SQL statement. It can appear in SELECT, FROM, WHERE, HAVING, INSERT, UPDATE, DELETE, and other contexts. Subqueries can be scalar, multi-row, correlated, or used as derived tables. The optimizer may transform logically equivalent joins and subqueries into similar execution strategies, so performance should be evaluated from the actual plan rather than syntax alone.
A scalar subquery is expected to return a single value: one column from at most one row. It can be used anywhere a scalar expression is valid. If a scalar subquery returns more than one row, SQL Server raises an error. Aggregate functions such as MAX, MIN, or AVG are often used when a single result is required.
A correlated subquery references a column from the outer query. Logically, it is evaluated in relation to each outer row, although the SQL Server optimizer may transform the physical execution. Correlated subqueries are commonly used with EXISTS and NOT EXISTS to test whether related rows are present.
EXISTS tests whether a subquery returns at least one row. The values selected inside the EXISTS subquery are not important; SELECT 1 is a common convention. EXISTS is especially useful for semi-join logic such as returning customers who have orders or categories that contain products. SQL Server can stop searching for a match once existence has been established, depending on the execution plan.
IN compares an expression against a list or subquery result, while EXISTS tests whether qualifying rows exist. For many logically equivalent queries, SQL Server can generate similar plans. However, NULL semantics differ and can become especially important with NOT IN. EXISTS and NOT EXISTS are often preferred when expressing relationship existence or non-existence. Performance should be determined from indexes, cardinality, statistics, and execution plans rather than assuming one syntax is always faster.
Both can express exclusion, but NOT IN can behave unexpectedly when its comparison set contains NULL. Because SQL uses three-valued logic, the presence of NULL can cause the predicate to evaluate as UNKNOWN rather than TRUE. NOT EXISTS is generally safer for anti-join logic when NULLs may be present. Alternatively, NULLs must be explicitly excluded from the NOT IN subquery.
A Common Table Expression is a named query expression defined with WITH and referenced by the statement immediately following it. CTEs can improve readability by breaking a complex query into logical parts. They are not automatically materialized temporary result sets; the optimizer determines how the query is executed. A non-recursive CTE generally exists only for the scope of the following statement.
A recursive CTE references itself and is commonly used to traverse hierarchical data such as employee reporting structures, organizational trees, or category hierarchies. It normally contains an anchor member that returns the starting rows and a recursive member that repeatedly joins to the previous result. UNION ALL combines the members. SQL Server's default MAXRECURSION limit for a recursive query is 100 unless changed with an OPTION clause. Cycles and excessive recursion must be considered in production designs.
UNION combines the results of two or more compatible SELECT statements and removes duplicate rows from the combined result. Each SELECT must return the same number of columns, and corresponding columns must have compatible data types. Because duplicate elimination can require additional work, UNION ALL is preferable when duplicates do not need to be removed.
UNION combines compatible result sets and removes duplicates. UNION ALL combines them without duplicate elimination. UNION ALL is generally less expensive because SQL Server does not need to perform the extra work required to enforce distinctness. Therefore, use UNION ALL when duplicate rows are acceptable or when the source sets are already known to be disjoint.
INTERSECT returns distinct rows that appear in both query results. EXCEPT returns distinct rows from the first query that do not appear in the second query. Like UNION, the participating SELECT statements must return the same number of columns with compatible data types. These operators are useful for comparison, reconciliation, and data-validation tasks. The order matters for EXCEPT: A EXCEPT B is not the same as B EXCEPT A.