Advanced T-SQL Join Techniques: Beyond INNER and LEFT Joins

While INNER and LEFT JOINs are the workhorses of SQL queries, T-SQL offers several more advanced join techniques that can be invaluable for complex data analysis and manipulation. This article explores some of these powerful join methods and how they can enhance our queries.

Full Outer Join

The FULL OUTER JOIN returns all rows from both tables, matching records where possible and including NULL values where there is no match.

Example join:

SELECT *
FROM TableA A
FULL OUTER JOIN TableB B ON A.ID = B.ID

Example result:

ID   ValueA  ValueB
---  ------  ------
1,   A1      null  
2,   A2      null  
3,3  A3      B3    
4,4  A4      B4    
5,5  A5      B5    
6,6  A6      B6    
7,7  A7      B7    
8    null    B8    
9    null    B9

This result is based on the two tables having the following data:

SELECT * FROM TableA;
ID  ValueA
--  ------
1   A1    
2   A2    
3   A3    
4   A4    
5   A5    
6   A6    
7   A7    

SELECT * FROM TableB;
ID  ValueB
--  ------
3   B3    
4   B4    
5   B5    
6   B6    
7   B7    
8   B8    
9   B9

Use cases:

Identifying missing data between two tables
Combining datasets with partially overlapping information

Cross Join

The CROSS JOIN produces a Cartesian product of two tables, combining each row from the first table with every row from the second table.

SELECT *
FROM Colors
CROSS JOIN Sizes

Example result:

Color  Size  
-----  ------
Red    Small 
Red    Medium
Red    Large 
Red    XL    
Blue   Small 
Blue   Medium
Blue   Large 
Blue   XL    
Green  Small 
Green  Medium
Green  Large 
Green  XL

Raw data:

SELECT * FROM Colors;
Color
-----
Red  
Blue 
Green

SELECT * FROM Sizes;
Size  
------
Small 
Medium
Large 
XL

Use cases:

Generating combinations for analysis
Creating lookup tables

Self Join

A SELF JOIN is when a table is joined with itself, typically used for hierarchical or sequential data within a single table.

SELECT e1.EmployeeName, e2.EmployeeName AS ManagerName
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID

Example result:

EmployeeName   ManagerName  
-------------  -------------
John Doe       null         
Jane Smith     John Doe     
Bob Johnson    John Doe     
Alice Brown    Jane Smith   
Charlie Davis  Jane Smith   
Eva Wilson     Bob Johnson  
Frank Miller   Bob Johnson  
Grace Lee      Alice Brown  
Henry Taylor   Charlie Davis

The Employees table contains the following data:

EmployeeID  EmployeeName   ManagerID
----------  -------------  ---------
1           John Doe       null     
2           Jane Smith     1        
3           Bob Johnson    1        
4           Alice Brown    2        
5           Charlie Davis  2        
6           Eva Wilson     3        
7           Frank Miller   3        
8           Grace Lee      4        
9           Henry Taylor   5

Use cases:

Organisational hierarchies
Finding sequential relationships

`CROSS APPLY` and `OUTER APPLY`

APPLY operators allow us to join a table with a table-valued function or subquery that depends on the first table.

SELECT O.OrderID, O.CustomerName, TopItems.*
FROM Orders O
CROSS APPLY (SELECT TOP 3 * FROM OrderDetails OD 
             WHERE OD.OrderID = O.OrderID 
             ORDER BY Quantity DESC) AS TopItems;

Example result:

OrderID  CustomerName  OrderDetailID  ProductName  Quantity
-------  ------------  -------------  -----------  --------
1,1      Customer A    1              Product X    5       
1,1      Customer A    2              Product Y    3       
1,1      Customer A    3              Product Z    2       
2,2      Customer B    6              Product Y    4       
2,2      Customer B    5              Product X    2       
2,2      Customer B    7              Product Z    1       
3,3      Customer C    10             Product Z    5       
3,3      Customer C    11             Product W    4       
3,3      Customer C    8              Product X    3

Raw data:

SELECT * FROM Orders;
OrderID  CustomerName  OrderDate               
-------  ------------  ------------------------
1        Customer A    2023-01-15T00:00:00.000Z
2        Customer B    2023-02-20T00:00:00.000Z
3        Customer C    2023-03-25T00:00:00.000Z

SELECT * FROM OrderDetails;
OrderDetailID  OrderID  ProductName  Quantity
-------------  -------  -----------  --------
1              1        Product X    5       
2              1        Product Y    3       
3              1        Product Z    2       
4              1        Product W    1       
5              2        Product X    2       
6              2        Product Y    4       
7              2        Product Z    1       
8              3        Product X    3       
9              3        Product Y    2       
10             3        Product Z    5       
11             3        Product W    4

Use cases:

Executing correlated subqueries efficiently
Applying row-by-row operations

Non-Equi Joins

Joins don’t always have to use equality comparisons. Other operators can create different types of relationships.

SELECT E.EmployeeID, E.EmployeeName, E.Salary, S.SalaryGrade
FROM Employees E
JOIN Salaries S ON E.Salary BETWEEN S.MinSalary AND S.MaxSalary;

Example result:

EmployeeID  EmployeeName   Salary  SalaryGrade
----------  -------------  ------  -----------
8           Grace Lee      48000   Grade 1    
9           Henry Taylor   45000   Grade 1    
4           Alice Brown    70000   Grade 2    
5           Charlie Davis  65000   Grade 2    
6           Eva Wilson     55000   Grade 2    
7           Frank Miller   52000   Grade 2    
2           Jane Smith     90000   Grade 3    
3           Bob Johnson    85000   Grade 3    
1           John Doe       120000  Grade 4

Raw data:

SELECT * FROM Employees;
EmployeeID  EmployeeName   ManagerID  Salary
----------  -------------  ---------  ------
1           John Doe       null       120000
2           Jane Smith     1          90000 
3           Bob Johnson    1          85000 
4           Alice Brown    2          70000 
5           Charlie Davis  2          65000 
6           Eva Wilson     3          55000 
7           Frank Miller   3          52000 
8           Grace Lee      4          48000 
9           Henry Taylor   5          45000

SELECT * FROM Salaries;
SalaryID  SalaryGrade  MinSalary  MaxSalary
--------  -----------  ---------  ---------
1         Grade 1      30000      50000    
2         Grade 2      50001      75000    
3         Grade 3      75001      100000   
4         Grade 4      100001     150000

Use cases:

Range-based relationships
Fuzzy matching

Joining on Multiple Conditions

Joins can use multiple conditions to create more precise relationships between tables.

SELECT O.OrderID, O.CustomerName, O.OrderDate, C.StartDate, C.EndDate
FROM Orders O
JOIN Customers C ON O.CustomerName = C.CustomerName 
                 AND O.OrderDate BETWEEN C.StartDate AND C.EndDate;

Example result:

OrderID  CustomerName  OrderDate                 StartDate                 EndDate                 
-------  ------------  ------------------------  ------------------------  ------------------------
1        Customer A    2023-01-15T00:00:00.000Z  2023-01-01T00:00:00.000Z  2023-06-30T00:00:00.000Z
4        Customer A    2023-02-10T00:00:00.000Z  2023-01-01T00:00:00.000Z  2023-06-30T00:00:00.000Z
7        Customer A    2023-05-25T00:00:00.000Z  2023-01-01T00:00:00.000Z  2023-06-30T00:00:00.000Z
2        Customer B    2023-02-20T00:00:00.000Z  2023-02-01T00:00:00.000Z  2023-07-31T00:00:00.000Z
5        Customer B    2023-03-15T00:00:00.000Z  2023-02-01T00:00:00.000Z  2023-07-31T00:00:00.000Z
8        Customer B    2023-06-30T00:00:00.000Z  2023-02-01T00:00:00.000Z  2023-07-31T00:00:00.000Z
3        Customer C    2023-03-25T00:00:00.000Z  2023-03-01T00:00:00.000Z  2023-08-31T00:00:00.000Z
6        Customer C    2023-04-20T00:00:00.000Z  2023-03-01T00:00:00.000Z  2023-08-31T00:00:00.000Z
9        Customer C    2023-07-05T00:00:00.000Z  2023-03-01T00:00:00.000Z  2023-08-31T00:00:00.000Z

Raw data:

SELECT * FROM Customers;
CustomerID  CustomerName  StartDate                 EndDate                 
----------  ------------  ------------------------  ------------------------
1           Customer A    2023-01-01T00:00:00.000Z  2023-06-30T00:00:00.000Z
2           Customer B    2023-02-01T00:00:00.000Z  2023-07-31T00:00:00.000Z
3           Customer C    2023-03-01T00:00:00.000Z  2023-08-31T00:00:00.000Z

SELECT * FROM Orders;
OrderID  CustomerName  OrderDate               
-------  ------------  ------------------------
1        Customer A    2023-01-15T00:00:00.000Z
2        Customer B    2023-02-20T00:00:00.000Z
3        Customer C    2023-03-25T00:00:00.000Z
4        Customer A    2023-02-10T00:00:00.000Z
5        Customer B    2023-03-15T00:00:00.000Z
6        Customer C    2023-04-20T00:00:00.000Z
7        Customer A    2023-05-25T00:00:00.000Z
8        Customer B    2023-06-30T00:00:00.000Z
9        Customer C    2023-07-05T00:00:00.000Z

Use cases:

Time-based relationships
Complex business rules

By mastering these advanced join techniques, we can write more efficient and expressive T-SQL queries, handling complex data relationships with ease. Each method has its own strengths and use cases, so we should be sure to choose the right technique based on our specific data and analytical needs.

Full Outer Join

Cross Join

Self Join

CROSS APPLY and OUTER APPLY

Non-Equi Joins

Joining on Multiple Conditions

`CROSS APPLY` and `OUTER APPLY`