Interview Questions and Answers on Joins and Subqueries | SQL Server

Joins and subqueries are fundamental concepts in SQL Server that enable you to retrieve and manipulate data across multiple tables. Understanding these concepts is essential for passing SQL interviews. Below are 30 interview questions and answers, categorized by topics, to help you master joins, subqueries, and Common Table Expressions (CTEs).

Joins and Subqueries | SQL Server

1. Types of Joins (INNER, LEFT, RIGHT, FULL)

1. What is an INNER JOIN in SQL Server?
   • An INNER JOIN returns only the rows where there is a match in both tables. If there is no match, the row is excluded from the result set.
2. How does a LEFT JOIN work in SQL Server?
   • A LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match is found, the result will contain NULL values for the right table’s columns.
3. What is a RIGHT JOIN in SQL Server?
   • A RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match is found, the result will contain NULL values for the left table’s columns.
4. What is a FULL JOIN in SQL Server?
   • A FULL JOIN returns all rows when there is a match in either the left or right table. If no match exists, the result will contain NULL values for the missing side’s columns.
5. How do INNER JOIN and LEFT JOIN differ in SQL Server?
   • An INNER JOIN returns only matched rows, while a LEFT JOIN returns all rows from the left table, even if there is no match in the right table.
6. What is the performance difference between INNER JOIN and LEFT JOIN in SQL Server?
   • Generally, INNER JOIN is more efficient than LEFT JOIN because it only returns matching rows, while LEFT JOIN has to include all rows from the left table, even if no match exists.
7. Can you use a JOIN on multiple tables?
   • Yes, you can use multiple joins in a single query to retrieve data from more than two tables. The joins can be any combination of INNER JOIN, LEFT JOIN, etc.
8. What is a CROSS JOIN in SQL Server?
   • A CROSS JOIN returns the Cartesian product of two tables, meaning it returns all possible combinations of rows from both tables, without any condition for matching rows.
9. What is the difference between a SELF JOIN and a regular JOIN in SQL Server?
   • A SELF JOIN is a join of a table with itself. It is useful for hierarchical data, such as finding relationships like employees and managers in the same table.
10. How can you filter the results of a JOIN in SQL Server?
    • You can use a WHERE clause to filter the rows returned by a join, typically by specifying conditions that match data from the joined tables.

2. Subqueries (Correlated and Non-Correlated)

11. What is a subquery in SQL Server?
    • A subquery is a query nested within another query. It can return a single value, multiple values, or a table, and is used to filter or perform operations within a parent query.
12. What is a correlated subquery in SQL Server?
    • A correlated subquery is a subquery that references columns from the outer query. It is evaluated for each row of the outer query and can return different results for each row.
13. What is a non-correlated subquery in SQL Server?
    • A non-correlated subquery is independent of the outer query and can be executed independently. It returns a single result that is used by the outer query.
14. Can you provide an example of a correlated subquery?
    • Example: SELECT employee_name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);. Here, the subquery uses the outer query’s column e.department_id.
15. Can a subquery be used in a SELECT statement?
    • Yes, subqueries can be used in a SELECT statement. They can return values to be displayed in the result set or used for calculations.
16. What is the difference between an EXISTS and an IN operator in SQL Server?
    • EXISTS checks if a subquery returns any rows, while IN checks if a value matches any value returned by a subquery. EXISTS is generally used with correlated subqueries.
17. What is a scalar subquery in SQL Server?
    • A scalar subquery returns a single value (a single row and single column). It can be used in the SELECT, WHERE, or HAVING clauses of a query.
18. How do you optimize subqueries in SQL Server?
    • To optimize subqueries, consider using joins instead of subqueries when possible, avoid using SELECT * inside subqueries, and ensure indexes are present on the columns involved in the subquery.
19. Can a subquery return multiple rows?
    • Yes, a subquery can return multiple rows. However, if the subquery is used in a place where a single value is expected (like in a comparison), SQL Server will raise an error.
20. Can a subquery be used in an UPDATE or DELETE statement in SQL Server?
    • Yes, subqueries can be used in UPDATE and DELETE statements to identify which rows should be updated or deleted, based on the results of the subquery.

3. Common Table Expressions (CTEs)

21. What is a Common Table Expression (CTE) in SQL Server?
    • A CTE is a temporary result set defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It is used for simplifying complex queries.
22. How do you define a CTE in SQL Server?
    • A CTE is defined using the WITH keyword, followed by the CTE name, an optional column list, and the query that defines the CTE. For example: WITH CTE_Name AS (SELECT * FROM table) SELECT * FROM CTE_Name;.
23. What is the difference between a CTE and a subquery in SQL Server?
    • A CTE is defined before the main query and can be referenced multiple times within the query. A subquery, on the other hand, is nested within the query and can only be used once.
24. How can you use a CTE to perform recursive queries in SQL Server?
    • A recursive CTE is a CTE that references itself to retrieve hierarchical data. It has two parts: the anchor member (initial query) and the recursive member (which references the CTE).
25. Can a CTE be used in an UPDATE statement in SQL Server?
    • Yes, CTEs can be used in UPDATE statements. They allow for easier reference of complex calculations or subqueries while updating data in a table.
26. Can you join a CTE with other tables in SQL Server?
    • Yes, a CTE can be joined with other tables in the main query. This makes it easier to work with derived result sets and simplifies complex joins.
27. What is the advantage of using CTEs over subqueries in SQL Server?
    • CTEs improve readability, can be referenced multiple times within a query, and allow for easier debugging. They are particularly useful for complex or recursive queries.
28. How can you limit the number of rows returned by a CTE in SQL Server?
    • You can limit the number of rows in a CTE by using the TOP keyword or applying filters in the SELECT query that follows the CTE definition.
29. What is the scope of a CTE in SQL Server?
    • The scope of a CTE is limited to the query immediately following its definition. Once the query completes, the CTE is no longer available.
30. Can a CTE reference itself?
    • Yes, a CTE can reference itself in a recursive query. This allows it to traverse hierarchical data, such as organizational structures or file systems.