CHAPTER 6: Building on Subqueries, Common Table Expressions, and Unions

Question 1

Q: What is a subquery in T-SQL?

Answer 1

A: A subquery is a nested query—a query within another query—that can return results for use in the main query.

Question 2

Q: Where can subqueries be used in a SQL query?

Answer 2

A: Subqueries can be used in the SELECT, FROM, and WHERE clauses, or as part of other expressions.

Question 3

Q: How is a subquery used in an IN list?

Answer 3

A: A subquery can replace a hard-coded list in the WHERE clause using the syntax:

SELECT <columns>
FROM <table>
WHERE <column> IN (SELECT <column> FROM <table>);</column></column></columns>

Question 4

Q: What is the difference between joining tables and using a subquery in the WHERE clause?

Answer 4

A: A join includes columns from both tables in the result, while a subquery returns a filtered list of values for comparison in the main query.

Question 5

Q: How do you use a subquery to find rows not in another table?

Answer 5

A: Add NOT before IN in the query:

SELECT <columns>
FROM <table1>
WHERE <column> NOT IN (SELECT <column> FROM <table2>);</table2></column></column></table1></columns>

Question 6

Q: What happens if a subquery returns a NULL in a NOT IN clause?

Answer 6

A: If the subquery returns any NULL values, the NOT IN clause will return no rows due to SQL’s three-valued logic (TRUE, FALSE, UNKNOWN).

Question 7

Q: How can you handle NULL values in a subquery?

Answer 7

A: Add a WHERE condition in the subquery to exclude NULL values:

SELECT <columns>
FROM <table1>
WHERE <column> NOT IN (SELECT <column> FROM <table2> WHERE <column> IS NOT NULL);</column></table2></column></column></table1></columns>

Question 8

Q: Why is it important to eliminate NULL in subqueries?

Answer 8

A: To ensure accurate results when using operators like NOT IN, which cannot handle NULL values correctly without additional filtering.

Question 9

Q: When should you use a subquery instead of a join?

Answer 9

A: Use subqueries when you need to filter data without including additional columns from the related table in the result.

Question 10

Q: Can subqueries return more than one column?

Answer 10

A: Subqueries in the WHERE clause must return only one column, but subqueries in other clauses (e.g., FROM) can return multiple columns.

Question 11

Q: What is the purpose of the EXISTS keyword in T-SQL?

Answer 11

A: EXISTS checks whether a subquery returns any rows. If rows exist, it evaluates to TRUE for the outer query.

Question 12

Q: How does the EXISTS clause work in the WHERE statement?

Answer 12

A: It uses a correlated subquery to determine if rows in the subquery match the outer query condition.

SELECT CustomerID, AccountNumber
FROM Sales.Customer AS Cust
WHERE EXISTS (SELECT 1 FROM Sales.SalesOrderHeader AS SOH WHERE SOH.CustomerID = Cust.CustomerID);

Question 13

Q: What does NOT EXISTS do in T-SQL?

Answer 13

A: It returns TRUE for rows in the outer query if no rows in the subquery meet the condition.

SELECT CustomerID, AccountNumber
FROM Sales.Customer AS Cust
WHERE NOT EXISTS (SELECT 1 FROM Sales.SalesOrderHeader AS SOH WHERE SOH.CustomerID = Cust.CustomerID);

Question 14

Q: Why can you use SELECT * or SELECT 1 in EXISTS subqueries?

Answer 14

A: The subquery only checks for the presence of rows, not the returned data. Both forms are functionally identical.

Question 15

Q: What is the purpose of CROSS APPLY?

Answer 15

A: CROSS APPLY joins each row from the left table with the results of a subquery. The subquery runs once for each row in the left table and returns only rows that match.

Question 16

Q: How does CROSS APPLY differ from a regular join?

Answer 16

A: CROSS APPLY allows you to use a subquery that can reference columns from the outer query, enabling row-by-row computation.

Question 17

Q: What is OUTER APPLY in T-SQL?

Answer 17

A: OUTER APPLY works like a LEFT OUTER JOIN. It returns all rows from the left table, including rows where the subquery does not return results (those will have NULL values).

Question 18

Q: Provide an example of using CROSS APPLY.

Answer 18

SELECT CustomerID, AccountNumber, SalesOrderID
FROM Sales.Customer AS Cust
CROSS APPLY (SELECT SalesOrderID FROM Sales.SalesOrderHeader AS SOH WHERE Cust.CustomerID = SOH.CustomerID) AS A;

Question 19

Q: Provide an example of using OUTER APPLY.

Answer 19

SELECT CustomerID, AccountNumber, SalesOrderID
FROM Sales.Customer AS Cust
OUTER APPLY (SELECT SalesOrderID FROM Sales.SalesOrderHeader AS SOH WHERE Cust.CustomerID = SOH.CustomerID) AS A;

Question 20

Q: When should you use EXISTS over a join or subquery in the WHERE clause?

Answer 20

A: Use EXISTS when you only need to verify the existence of matching rows in a subquery without returning actual data from it.

Question 21

Q: What is the main difference between CROSS APPLY and OUTER APPLY?

Answer 21

A: CROSS APPLY behaves like an INNER JOIN, only returning rows where the subquery produces results, whereas OUTER APPLY behaves like a LEFT OUTER JOIN, returning all rows from the left table.

Question 22

Q: Why might EXISTS perform better than other techniques?

Answer 22

A: EXISTS stops evaluating as soon as it finds a match, making it efficient for large datasets with selective filtering.

Question 23

Q: What is the purpose of the UNION operator in T-SQL?

Answer 23

A: UNION combines the results of two or more queries into a single result set, removing duplicate rows by default.

Question 24

Q: What is the difference between UNION and UNION ALL?

Answer 24

A: UNION removes duplicate rows, while UNION ALL includes all rows, even duplicates, which improves performance if deduplication is unnecessary.

Question 25

Q: What must match between the queries in a UNION statement?

Answer 25

A: Each query must return the same number of columns, and the columns must have compatible data types.

Question 26

Q: Provide the syntax for a simple UNION query.

Answer 26

SELECT col1, col2 FROM table1
UNION
SELECT col1, col2 FROM table2;

Question 27

Q: Why should you use UNION ALL when possible?

Answer 27

A: UNION ALL avoids the overhead of removing duplicates, which improves performance, especially with large datasets.

Question 28

Q: Where must the ORDER BY clause be placed in a UNION query?

Answer 28

A: The ORDER BY clause must be placed at the end of the entire UNION query, not after individual queries.
Example:

SELECT col1 FROM table1
UNION
SELECT col1 FROM table2
ORDER BY col1;

Question 29

Q: How does SQL Server determine column names in a UNION query?

Answer 29

A: The column names and data types are determined by the first query in the UNION.

Question 30

Q: What happens if the columns or data types don’t match between queries in a UNION?

Answer 30

A: SQL Server will throw an error if the column counts differ or if incompatible data types cannot be implicitly converted.

Question 31

Q: Provide an example of a UNION query with duplicate elimination.

Answer 31

SELECT CustomerID FROM Sales.Customers
UNION
SELECT CustomerID FROM Sales.ArchivedCustomers;

Question 32

Q: Provide an example of a UNION ALL query to combine results without deduplication.

Answer 32

SELECT ProductID, Name FROM Products_Current
UNION ALL
SELECT ProductID, Name FROM Products_Discontinued;

Question 33

Q: What error will occur if the number of columns doesn’t match in a UNION query?

Answer 33

A: SQL Server will throw an error indicating a mismatch in the number of columns.

Question 34

Q: Why is it important to use compatible data types in UNION queries?

Answer 34

A: SQL Server follows precedence rules, and incompatible types (e.g., mixing strings and integers) can cause errors or unexpected implicit conversions.

Question 35

Q: What is a good use case for a UNION query?

Answer 35

A: Combining production and archived data or displaying data from different divisions of a company as a unified result.

Question 36

Q: Provide an example of a UNION query that uses the same table with different WHERE clauses.

Answer 36

SELECT CustomerID, OrderDate FROM Orders WHERE Year(OrderDate) = 2023
UNION
SELECT CustomerID, OrderDate FROM Orders WHERE Year(OrderDate) = 2022;

Question 37

Q: What is the purpose of the EXCEPT operator in T-SQL?

Answer 37

A: EXCEPT returns rows from the first query that are not present in the second query, removing duplicates by default.

Question 38

Q: Provide an example of an EXCEPT query.

Answer 38

SELECT BusinessEntityID
FROM HumanResources.Employee
EXCEPT
SELECT BusinessEntityID
FROM Person.Person;

Question 39

Q: How does EXCEPT handle duplicates?

Answer 39

A: EXCEPT removes duplicates from the results unless explicitly overridden by using additional query constructs.

Question 40

Q: When should you use EXCEPT in queries?

Answer 40

A: Use EXCEPT when you need to find rows in one query that do not exist in another query, often for identifying mismatches or gaps.

Question 41

Q: What rules must be followed when using EXCEPT?

Answer 41

A: Both queries must return the same number of columns, and their data types must be compatible.

Question 42

Q: What does the INTERSECT operator do in T-SQL?

Answer 42

A: INTERSECT returns rows that exist in both queries, removing duplicates by default.

Question 43

Q: Provide an example of an INTERSECT query.

Answer 43

SELECT BusinessEntityID
FROM HumanResources.Employee
INTERSECT
SELECT BusinessEntityID
FROM Person.Person;

Question 44

Q: How does INTERSECT handle duplicate rows?

Answer 44

A: INTERSECT automatically removes duplicate rows, ensuring only unique common rows are returned.

Question 45

Q: When is it appropriate to use INTERSECT?

Answer 45

A: Use INTERSECT when you want to find common rows between two queries, such as overlapping records between two datasets.

Question 46

Q: What are the requirements for columns and data types in an INTERSECT query?

Answer 46

A: The columns in both queries must match in number and have compatible data types.

Question 47

Q: What is the difference between EXCEPT and INTERSECT?

Answer 47

A: EXCEPT returns rows from the first query that are not in the second query, while INTERSECT returns rows that are present in both queries.

Question 48

Q: Can EXCEPT or INTERSECT include an ORDER BY clause?

Answer 48

A: Yes, but the ORDER BY clause must be placed after the entire EXCEPT or INTERSECT operation, not within the individual queries.

Question 49

Q: How can EXCEPT be used for data quality checks?

Answer 49

A: EXCEPT can identify rows in one table that are missing in another, such as detecting missing foreign key references.

Question 50

Q: How can INTERSECT assist in verifying data consistency?

Answer 50

A: INTERSECT can be used to find rows that are common across datasets, ensuring data alignment between two sources.

Question 51

Q: What is a derived table in T-SQL?

Answer 51

A: A derived table is a subquery that appears in the FROM clause of a query and is treated like a temporary table.

Question 52

Q: Why use a derived table?

Answer 52

A: Derived tables isolate part of a query’s logic, making the query modular and easier to understand and manage.

Question 53

Q: What is the syntax for using a derived table?

Answer 53

SELECT <columns>
FROM (SELECT <columns> FROM <table>) AS <alias>;</alias></columns></columns>

Question 54

Q: Must a derived table be aliased?

Answer 54

A: Yes, a derived table always requires an alias to reference its columns in the outer query.

Question 55

Q: Can a derived table contain multiple tables or a WHERE clause?

Answer 55

A: Yes, a derived table can include joins, multiple tables, and a WHERE clause.

Question 56

Q: Can a derived table include an ORDER BY clause?

Answer 56

A: Only if the TOP keyword is used.

Question 57

Q: Can a derived table reference columns not included in its SELECT list?

Answer 57

A: No, all columns needed for joins or output in the outer query must be explicitly included in the derived table’s SELECT list.

Question 58

Q: Provide an example of a derived table in an INNER JOIN.

Answer 58

SELECT c.CustomerID, s.SalesOrderID
FROM Sales.Customer AS c
INNER JOIN (
SELECT SalesOrderID, CustomerID
FROM Sales.SalesOrderHeader
) AS s ON c.CustomerID = s.CustomerID;

Question 59

Q: How can derived tables be combined with joins?

Answer 59

A: Derived tables can be used with any type of join, such as INNER JOIN, LEFT JOIN, CROSS JOIN, or FULL JOIN.

Question 60

Q: Can derived tables reference a CTE?

Answer 60

A: Yes, a derived table can reference a CTE defined earlier in the query.

Question 61

Q: Can a derived table contain another derived table?

Answer 61

A: Yes, derived tables can be nested within other derived tables for complex queries.

Question 62

Q: What is one limitation of derived tables compared to other T-SQL techniques?

Answer 62

A: Derived tables cannot define their own CTEs within their scope.

Question 63

Q: When is it beneficial to use a derived table?

Answer 63

A: Use derived tables to encapsulate logic, simplify joins, or perform intermediate calculations.

Question 64

Q: What happens if a required column is omitted from the derived table’s SELECT list?

Answer 64

A: The query will fail because the outer query cannot access columns that are not explicitly selected in the derived table.

Question 65

Q: What is a Common Table Expression (CTE)?

Answer 65

A: A CTE is a temporary result set defined within a query that exists only for the duration of the query.

Question 66

Q: How does a CTE differ from a derived table?

Answer 66

A: Unlike a derived table, a CTE is defined separately using the WITH keyword and can be referenced multiple times in the main query.

Question 67

Q: What happens to a CTE after the main query runs?

Answer 67

A: A CTE goes out of scope and cannot be reused once the query has completed execution.

Question 68

Q: What is the basic syntax for defining a CTE?

Answer 68

WITH <CTE> AS (SELECT <columns> FROM <table>)
SELECT <columns> FROM <CTE>;</CTE></columns></columns></CTE>

Question 69

Q: How do you define column aliases in a CTE?

Answer 69

A: Specify the aliases after the CTE name:

WITH CTE_Name ([Column1], [Column2]) AS (
SELECT Column1, Column2 FROM Table
)
SELECT * FROM CTE_Name;

Question 70

Q: What is the required placement of the WITH keyword in a query?

Answer 70

A: The WITH keyword must be the first statement in the batch or be preceded by a semicolon if other statements appear before it.

Question 71

Q: Can a CTE include joins, WHERE clauses, or expressions?

Answer 71

A: Yes, a CTE can include joins, filters, expressions, and even reference other tables or views.

Question 72

Q: Provide an example of a CTE used for an INNER JOIN.

Answer 72

WITH Orders AS (
SELECT SalesOrderID, CustomerID, TotalDue + Freight AS Total
FROM Sales.SalesOrderHeader
)
SELECT c.CustomerID, o.SalesOrderID, o.Total
FROM Sales.Customer AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID;

Question 73

Q: What happens if a column alias is not defined in a CTE?

Answer 73

A: The column name from the CTE query is used, but defining aliases upfront can improve readability, especially for expressions.

Question 74

Q: Can a CTE be reused across multiple queries?

Answer 74

A: No, a CTE is valid only for the query in which it is defined.

Question 75

Q: Can a CTE reference another CTE?

Answer 75

A: Yes, one CTE can reference another within the same WITH clause.

Question 76

Q: Is it possible to use ORDER BY in a CTE?

Answer 76

A: Yes, but only when the TOP keyword is used.

Question 77

Q: How do you ensure the CTE is correctly referenced in complex queries?

Answer 77

A: Always use descriptive aliases and verify that required columns are included in the CTE’s SELECT list.

Question 78

Q: What advanced capabilities make CTEs superior to derived tables in some scenarios?

Answer 78

A: CTEs can be recursive, allowing them to solve hierarchical and iterative problems.

Question 79

Q: What is one major use case for CTEs in advanced queries?

Answer 79

A: CTEs are commonly used for breaking down complex logic into manageable parts or simplifying nested queries.

Question 80

Q: What is the main difference between UNION and UNION ALL?

Answer 80

A: UNION removes duplicate rows, while UNION ALL includes all rows, including duplicates.

Question 81

Q: Why does UNION perform worse than UNION ALL?

Answer 81

A: UNION uses additional resources to eliminate duplicates, often employing a Hash Match operation to aggregate results.

Question 82

Q: When should you use UNION ALL instead of UNION?

Answer 82

A: Use UNION ALL if you are certain there are no duplicates or duplicates are acceptable in the results.

Question 83

Q: How does SQL Server process duplicate elimination for UNION?

Answer 83

A: It uses the Hash Match Aggregate operator, which can be resource-intensive for large datasets.

Question 84

Q: Compare the estimated costs for UNION and UNION ALL from the example.

Answer 84

UNION query cost: 2.62912
UNION ALL query cost: 0.806502
UNION took ~77% of the resources in the batch.

Question 85

Q: How can you view the performance difference between UNION and UNION ALL?

Answer 85

A: Enable Include Actual Execution Plan in SQL Server Management Studio or use Explain in Azure Data Studio.

Question 86

Q: Why doesn’t wrapping a UNION ALL in a DISTINCT query improve performance?

Answer 86

A: The optimizer processes it like a UNION query internally, performing the same duplicate elimination steps.

Question 87

Q: When should you use DISTINCT with UNION ALL?

Answer 87

A: Use DISTINCT in individual queries if duplicates exist within the individual datasets but not across them.

Question 88

Q: Can using UNION ALL in a CTE or derived table help optimize performance?

Answer 88

A: No, unless the underlying queries ensure no duplicates. Otherwise, using DISTINCT or UNION negates performance gains.

Question 89

Q: What is a best practice for deciding between UNION and UNION ALL?

Answer 89

A: Always prefer UNION ALL if you don’t need to eliminate duplicates, as it improves performance by avoiding unnecessary operations.

Question 90

Q: What is a common operation used by SQL Server to remove duplicates in a UNION query?

Answer 90

A: The Hash Match Aggregate operation.

Question 91

Q: How can you combine improved performance and duplicate elimination if necessary?

Answer 91

A: Use DISTINCT within individual queries and combine them with UNION ALL.