CHAPTER 5: Joining Tables

Question 1

Q: What is the purpose of joining tables in T-SQL?

Answer 1

A: To combine relational data stored in multiple tables and present it as a single result set.

Question 2

Q: What type of relationship typically exists between two tables when they are joined?

Answer 2

A: A parent-child relationship, where the parent table has a primary key, and the child table has a foreign key referring to the parent.

Question 3

Q: Which type of join is most commonly used in T-SQL?

Answer 3

A: INNER JOIN.

Question 4

Q: What does an INNER JOIN do?

Answer 4

A: It returns only the rows that match in both tables based on the join condition.

Question 5

Q: Provide the basic syntax for an INNER JOIN.

Answer 5

SELECT <columns>
FROM <table1>
[INNER] JOIN <table2> ON <table1>.<col1> = <table2>.<col2>;</col2></table2></col1></table1></table2></table1></columns>

Question 6

Q: What happens if a row in the parent table does not have a matching row in the child table in an INNER JOIN?

Answer 6

A: The row from the parent table will not appear in the result set.

Question 7

Q: Why is it necessary to fully qualify column names in a join?

Answer 7

A: To avoid ambiguity when columns with the same name exist in both tables.

Question 7

Q: Write an example query to join Sales.SalesOrderHeader and Sales.SalesOrderDetail tables.

Answer 7

SELECT s.SalesOrderID, s.OrderDate, s.TotalDue,
d.SalesOrderDetailID, d.ProductID, d.OrderQty
FROM Sales.SalesOrderHeader AS s
INNER JOIN Sales.SalesOrderDetail AS d
ON s.SalesOrderID = d.SalesOrderID;

Question 8

Q: Why should you use table aliases in a query with joins?

Answer 8

A: To save typing and make the query more readable, especially when fully qualifying column names.

Question 9

Q: Why is using meaningful aliases for tables recommended over generic ones like “A” or “B”?

Answer 9

A: Meaningful aliases improve query readability and help identify the source of columns quickly.

Question 10

Q: What happens if you alias tables in a query?

Answer 10

A: The alias must be used to qualify column names instead of the full table name.

Question 11

Q: What is a composite primary key, and how is it used in joins?

Answer 11

A: A composite primary key consists of two or more columns that uniquely identify a row. It can be used as part of a join condition to match related rows in another table.

Question 12

Q: What is the primary factor that affects the performance of an INNER JOIN?

Answer 12

A: Indexes on the columns used in the join condition can significantly improve performance by enabling index seeks instead of scans.

Question 13

Q: What happens if the ON condition in a join always evaluates to TRUE, such as ON 1 = 1?

Answer 13

A: It creates a Cartesian product, where every row from the first table is joined with every row from the second table, resulting in an excessive and nonsensical number of rows.

Question 14

Q: What is a Cartesian product in SQL?

Answer 14

A: The result of an incorrect join condition where all rows from one table are combined with all rows from another table.

Question 15

Q: How can you avoid incorrect join conditions?

Answer 15

A: Ensure the ON clause correctly matches columns based on the relationship between the tables, such as matching foreign keys to primary keys.

Question 16

Q: Can you join tables if the columns have different names?

Answer 16

A: Yes, as long as the columns have compatible data types and a logical relationship.

Question 16

Q: Write an example of joining two tables with different column names.

Answer 16

SELECT c.CustomerID, c.PersonID, p.BusinessEntityID, p.LastName
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p
ON c.PersonID = p.BusinessEntityID;

Question 17

Q: What is a composite primary key?

Answer 17

A: A primary key composed of multiple columns, requiring all the columns in the key to uniquely identify a row.

Question 18

Q: How do you join tables on multiple columns?

Answer 18

A: Use the AND operator to include all the necessary column comparisons in the ON clause.
Example:

SELECT sod.SalesOrderID, sod.SalesOrderDetailID,
so.ProductID, so.SpecialOfferID
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SpecialOfferProduct AS so
ON so.ProductID = sod.ProductID
AND so.SpecialOfferID = sod.SpecialOfferID;

Question 19

Q: What happens if you omit one column in a multi-column join condition?

Answer 19

A: The query may produce incorrect results, showing rows that partially match but do not fully meet the intended relationship.

Question 20

Q: Why is understanding the relationship between tables critical when writing joins?

Answer 20

A: To ensure that the join condition accurately reflects the logical connections between the tables and avoids incorrect or nonsensical results.

Question 22

Q: Why should you test queries with incomplete join conditions?

Answer 22

A: To observe the impact of the missing condition and better understand the importance of writing accurate joins.

Question 23

Q: When would you need to join three or more tables?

Answer 23

A: When dealing with relationships like many-to-many or when data in one table indirectly connects two other tables, requiring all three for a complete result set.

Question 24

Q: Provide the syntax for joining three tables in T-SQL.

Answer 24

SELECT FROM [INNER] JOIN ON . = . [INNER] JOIN ON . = .;

Question 25

Q: Write a query to join Sales.SalesOrderHeader, Sales.SalesOrderDetail, and Production.Product tables to display sales order IDs, order dates, product IDs, and product names.

Answer 25

SELECT soh.SalesOrderID, soh.OrderDate, p.ProductID, p.Name FROM Sales.SalesOrderHeader AS soh INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID ORDER BY soh.SalesOrderID;

Question 26

Q: In a three-table join, why might some columns not appear in the final SELECT list?

Answer 26

A: The table serves as a bridge to connect the other two tables, and its columns may not be needed in the final output.

Question 27

Q: What approach can help if you have trouble figuring out how to join multiple tables?

Answer 27

A: Start by joining two tables and verify the results, then add the third table to refine the query step-by-step.

Question 28

Q: Why is it important to carefully choose join columns, especially when joining multiple tables?

Answer 28

A: Incorrect joins can lead to invalid results or Cartesian products, making the data meaningless.

Question 29

Q: How should you handle join columns with different names across tables?

Answer 29

A: Use fully qualified column names in the ON clause to specify the correct relationship.

Question 30

Q: How does a many-to-many relationship usually connect tables?

Answer 30

A: Through a junction table that acts as a bridge between two main tables.

Question 31

Q: What is a critical skill for T-SQL developers when working with relational databases?

Answer 31

A: Understanding and writing queries that effectively join multiple tables to produce accurate and meaningful results.

Question 32

Q: What is the purpose of OUTER JOIN in SQL?

Answer 32

A: OUTER JOIN retrieves all rows from one table and matching rows from another table, including unmatched rows with NULL values for non-matching columns.

Question 33

Q: What are the types of OUTER JOIN?

Answer 33

A: The types are LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

Question 34

Q: What does LEFT OUTER JOIN do?

Answer 34

A: It returns all rows from the left table and matching rows from the right table. If there is no match, the right table's columns are filled with NULL.

Question 35

Q: Provide the syntax for LEFT OUTER JOIN.

Answer 35

SELECT

FROM LEFT [OUTER] JOIN ON . = .;

Question 36

Q: Write a query to list all customers and their orders, including customers with no orders, using LEFT OUTER JOIN.

Answer 36

SELECT c.CustomerID, s.SalesOrderID, s.OrderDate FROM Sales.Customer AS c LEFT OUTER JOIN Sales.SalesOrderHeader AS s ON c.CustomerID = s.CustomerID WHERE c.CustomerID IN (11028,11029,1,2,3,4);

Question 37

Q: What happens when a row from the left table has no match in the right table in LEFT OUTER JOIN?

Answer 37

A: The columns from the right table for that row will contain NULL values.

Question 38

Q: What does RIGHT OUTER JOIN do?

Answer 38

A: It returns all rows from the right table and matching rows from the left table. If there is no match, the left table's columns are filled with NULL.

Question 42

Q: Provide the syntax for RIGHT OUTER JOIN.

Answer 42

SELECT FROM RIGHT [OUTER] JOIN ON . = .;

Question 42

Q: Write a query to list all customers and their orders, including customers with no orders, using RIGHT OUTER JOIN.

Answer 42

SELECT c.CustomerID, s.SalesOrderID, s.OrderDate FROM Sales.SalesOrderHeader AS s RIGHT OUTER JOIN Sales.Customer AS c ON c.CustomerID = s.CustomerID WHERE c.CustomerID IN (11028,11029,1,2,3,4);

Question 43

Q: Why is LEFT OUTER JOIN generally preferred over RIGHT OUTER JOIN?

Answer 43

A: Because using LEFT OUTER JOIN keeps the main table consistent as the leftmost table, making queries easier to understand and maintain when joining multiple tables.

Question 44

Q: What is the difference between LEFT OUTER JOIN and RIGHT OUTER JOIN?

Answer 44

A: LEFT OUTER JOIN returns all rows from the left table, while RIGHT OUTER JOIN returns all rows from the right table.

Question 45

Q: What do LEFT OUTER JOIN and RIGHT OUTER JOIN have in common?

Answer 45

A: Both return all rows from one table and matching rows from the other table, filling unmatched columns with NULL.

Question 46

Q: How can you use LEFT OUTER JOIN to find rows with no match in another table?

Answer 46

A: By adding a WHERE clause that checks for NULL in the columns of the right-side table. Example: SELECT c.CustomerID FROM Sales.Customer AS c LEFT OUTER JOIN Sales.SalesOrderHeader AS s ON c.CustomerID = s.CustomerID WHERE s.SalesOrderID IS NULL;

Question 47

Q: What does the LEFT OUTER JOIN return when there is no matching row in the right table?

Answer 47

A: It returns all rows from the left table, with NULL in the columns from the right table for nonmatching rows.

Question 48

Q: Write the syntax for a query using LEFT OUTER JOIN to find rows without a match.

Answer 48

SELECT FROM LEFT OUTER JOIN ON WHERE IS NULL;

Question 49

Q: How do you join three tables with LEFT OUTER JOIN while ensuring unmatched rows remain in the results?

Answer 49

A: Use LEFT OUTER JOIN between all table pairs and ensure the leftmost table includes all rows. Example: SELECT C.CustomerID, SOH.SalesOrderID, SOD.SalesOrderDetailID FROM Sales.Customer AS C LEFT OUTER JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID = SOH.CustomerID LEFT OUTER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID;

Question 53

Q: Why must you continue to use LEFT OUTER JOIN when adding more tables to the query?

Answer 53

A: To ensure that unmatched rows from the primary (leftmost) table persist in the result set.

Question 53

Q: What happens if you change a LEFT OUTER JOIN to INNER JOIN in a multi-table query?

Answer 53

A: Rows with NULL in the join condition will be excluded, potentially removing unmatched rows from the result set.

Question 54

Q: What is a practical use case for finding rows with no match using LEFT OUTER JOIN?

Answer 54

A: To find customers who have not placed any orders.

Question 54

Q: When joining multiple tables, why is it recommended to use LEFT OUTER JOIN consistently?

Answer 54

A: It simplifies the query structure and avoids switching between LEFT and RIGHT joins, which can be confusing.

Question 57

Q: What should you consider when writing queries with LEFT OUTER JOIN?

Answer 57

A: Ensure the main table is on the left, fully qualify column names, and use WHERE clauses carefully to avoid inadvertently excluding rows.

Question 57

Q: What does the following query do? SELECT c.CustomerID, s.SalesOrderID, s.OrderDate FROM Sales.Customer AS c LEFT OUTER JOIN Sales.SalesOrderHeader AS s ON c.CustomerID = s.CustomerID LEFT OUTER JOIN Sales.SalesOrderDetail AS sod ON s.SalesOrderID = sod.SalesOrderID;

Answer 57

A: It retrieves all customers, their orders (if any), and the details of those orders. Customers without orders still appear with NULL values in the order and detail columns.

Question 58

Q: When adding a table to the main table of a LEFT JOIN, which type of join should you use to avoid losing rows?

Answer 58

A: Use LEFT OUTER JOIN to ensure that no rows from the main table are lost, even if there are no matching rows in the new table.

Question 59

Q: Why might it be safe to use INNER JOIN instead of LEFT OUTER JOIN in certain cases?

Answer 59

A: If you know that every row in the main table has a corresponding match in the new table (e.g., customers must always have a territory), then INNER JOIN can safely be used.

Question 60

Q: Write a query to add a Territory table to a LEFT JOIN query involving Customer, SalesOrderHeader, and SalesOrderDetail.

Answer 60

SELECT C.CustomerID, SOH.SalesOrderID, SOD.SalesOrderDetailID, SOD.ProductID, T.Name FROM Sales.Customer AS C LEFT OUTER JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID = SOH.CustomerID LEFT OUTER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID LEFT OUTER JOIN Sales.SalesTerritory AS T ON C.TerritoryID = T.TerritoryID WHERE C.CustomerID IN (11028,11029,1,2,3,4);

Question 61

Q: What does FULL OUTER JOIN return?

Answer 61

It returns all rows from both tables: Rows with matches are returned as one combined row. Rows from the left table with no match in the right table have NULL values in the right table's columns. Rows from the right table with no match in the left table have NULL values in the left table's columns.

Question 62

Q: Write the syntax for a FULL OUTER JOIN.

Answer 62

SELECT FROM FULL [OUTER] JOIN ON . = .;

Question 63

Q: Provide an example of a FULL OUTER JOIN query using a Product table and a ProductColor table.

Answer 63

SELECT c.Color AS "Color from list", p.Color, p.ProductID FROM Production.Product AS p FULL OUTER JOIN Production.ProductColor AS c ON p.Color = c.Color ORDER BY p.ProductID;

Question 64

Q: What is a practical use of FULL OUTER JOIN?

Answer 64

A: It is useful for finding data discrepancies, such as values in one table that do not exist in another, to clean up data before loading it into a production system or data warehouse.

Question 65

Q: What happens when there is no match in a FULL OUTER JOIN for a row in one of the tables?

Answer 65

For unmatched rows from the left table, columns from the right table will contain NULL. For unmatched rows from the right table, columns from the left table will contain NULL.

Question 66

Q: What does a CROSS JOIN do?

Answer 66

A: A CROSS JOIN combines every row from one table with every row from another table, creating a Cartesian product.

Question 67

Q: Write the syntax for a CROSS JOIN.

Answer 67

SELECT FROM CROSS JOIN ;

Question 67

Q: When is CROSS JOIN typically used?

Answer 67

A: It is used when creating all possible combinations of rows, such as generating inventory worksheets or matching every product with every location.

Question 68

Q: Provide an example of a CROSS JOIN query using Product and Location.

Answer 68

SELECT p.ProductID, l.LocationID FROM Production.Product AS p CROSS JOIN Production.Location AS l ORDER BY ProductID;

Question 70

Q: What is a potential risk of using a CROSS JOIN unintentionally?

Answer 70

A: It can generate an extremely large number of rows, consuming significant resources and potentially causing the query to hang.

Question 71

Q: What is a self-join?

Answer 71

A: A self-join is a query where a table is joined to itself, often to represent hierarchical or unary relationships.

Question 72

Q: Why do we need to use aliases in a self-join?

Answer 72

A: Aliases are required because the same table is used twice in the query, and SQL does not allow two identical table names in a single query.

Question 73

Q: Write the syntax for a self-join using LEFT OUTER JOIN.

Answer 73

SELECT a.col1, b.col2 FROM AS a LEFT OUTER JOIN AS b ON a.= b.;

Question 74

Q: What is an example use case of a self-join?

Answer 74

A: A self-join is often used to represent manager-employee relationships where both managers and employees are stored in the same table.

Question 75

Q: Provide a self-join example to show employee-manager relationships.

Answer 75

SELECT a.EmployeeID AS Employee, a.Title AS EmployeeTitle, b.EmployeeID AS ManagerID, b.Title AS ManagerTitle FROM #Employee AS a LEFT OUTER JOIN #Employee AS b ON a.ManagerID = b.EmployeeID;

Question 76

Q: Why is LEFT OUTER JOIN used in a self-join for hierarchical data?

Answer 76

A: It ensures that rows with no matching parent (e.g., a CEO with no manager) are still included in the results.

Question 77

Q: What is a Merge Join in SQL Server?

Answer 77

A: A Merge Join is a physical operator used when both inputs are sorted on the join key and the optimizer estimates a relatively large number of rows from each side.

Question 78

Q: When is a Merge Join preferred?

Answer 78

A: It is preferred when both inputs are sorted by the join key, such as when the columns used in the join have clustered or covering indexes.

Question 79

Q: What is the key benefit of a Merge Join?

Answer 79

A: It is highly efficient because it processes sorted data in a single pass for each side, minimizing comparisons.

Question 80

Q: What is a drawback of a Merge Join?

Answer 80

A: It requires both inputs to be sorted, which can be a costly operation if no suitable indexes exist.

Question 81

Q: What is a Nested Loop Join?

Answer 81

A: A Nested Loop Join is a physical operator where the database engine iterates through rows in one input (usually smaller) and searches for matches in the other input.

Question 84

Q: What are the advantages of a Nested Loop Join?

Answer 84

A: It is efficient for small datasets and works well when indexes exist on the larger input.

Question 84

Q: When does SQL Server use a Nested Loop Join?

Answer 84

A: It is used when one side of the join has a small number of rows, regardless of whether the data is sorted.

Question 85

Q: What are the disadvantages of a Nested Loop Join?

Answer 85

A: It becomes inefficient when the smaller input grows larger, as the cost increases with the number of rows in the smaller input.

Question 86

Q: What is a Hash Match Join?

Answer 86

A: A Hash Match Join uses hash tables to join two inputs. It creates hash tables for one input and probes them with the second input to find matches.

Question 87

Q: When is a Hash Match Join used?

Answer 87

A: It is used when inputs are not sorted, and the optimizer estimates that a large number of rows will be returned from both sides.

Question 88

Q: What are the benefits of a Hash Match Join?

Answer 88

A: It handles unsorted data efficiently and is useful for joining large datasets when sorting is not feasible.

Question 89

Q: What are the limitations of a Hash Match Join?

Answer 89

A: It uses more memory and CPU resources than a Merge Join and can be slower if the hash table spills to disk.

Question 90

Q: How can indexes affect join performance?

Answer 90

A: Proper indexes (especially on foreign keys) can reduce the cost of joins by enabling efficient seeks or sorted inputs for Merge Joins.

Question 91

Q: How does SQL Server choose the join operator?

Answer 91

A: The optimizer selects the best join operator (Merge, Nested Loop, or Hash) based on input sizes, indexes, sorting, and estimated row counts.

Question 92

Q: What can you do to optimize joins?

Answer 92

A: Create appropriate indexes, avoid unnecessary columns in SELECT, and ensure join conditions use indexed columns when possible.

Data Analyst (6 decks)

• Chapter 3: Writing Simple SELECT Queries
• CHAPTER 4: Using Built-in Functions and Expressions
• CHAPTER 5: Joining Tables
• CHAPTER 6: Building on Subqueries, Common Table Expressions, and Unions
• CHAPTER 7: Grouping and Summarizing Data
• CHAPTER 8: Discovering Windowing Functions

• Key Links

  • Pricing
  • Corporate Training
  • Teachers & Schools
  • iOS App
  • Android App
  • Help Center

• Subjects

  • Medical & Nursing
  • Law Education
  • Foreign Languages
  • All Subjects A-Z
  • All Certified Classes

• Company

  • About Us
  • Earn Money!
  • Academy
  • Swag Shop
  • Contact
  • Terms
  • Podcasts
  • Careers

• Find Us

  • 
  • 
  • 
  • 
  • 
  • 
  • 
  

  

  

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions