Advanced Joins in SQL Server - Examples and Best Practices

Write a SELF JOIN Query

Using an Employees table with EmployeeID and ManagerID, write a query to list each employee with their manager's name.

Expected output: A list showing employee names alongside their manager names.

Hint: Use table aliases and join Employees to itself on ManagerID = EmployeeID.

Generate All Combinations with CROSS JOIN

Given tables Colors and Sizes, write a query to produce all possible color-size pairs.

Expected output: A result set pairing every color with every size.

Hint: Use CROSS JOIN without an ON clause.

Combine Customers and Orders with FULL OUTER JOIN

Write a query to list all customers and orders, including customers without orders and orders without customers.

Expected output: A combined list showing all customers and orders with NULLs where no match exists.

Hint: Use FULL OUTER JOIN on CustomerID.

A SELF JOIN is a join where a table is joined to itself using aliases. It is used to compare rows within the same table, such as finding hierarchical relationships like employee-manager pairs.

A CROSS JOIN returns the Cartesian product of two tables, pairing every row from the first with every row from the second, without any join condition. An INNER JOIN returns only rows where there is a matching condition between tables.

A FULL OUTER JOIN returns all rows from both tables, including matched rows and unmatched rows from either side, filling missing columns with NULLs.