SQL Querying: Joins

MSDN Documentation - Database Services

Understanding SQL Joins

SQL Joins are used to combine rows from two or more tables based on a related column between them. This allows you to retrieve data that spans across multiple tables in your database. The most common type of join is based on the foreign key relationship between tables.

Types of Joins

There are several types of JOIN clauses in SQL, each serving a different purpose:

INNER JOIN (or JOIN)

Returns only the rows where there is a match in both tables. If a row in one table does not have a corresponding match in the other table, it is excluded from the result.

Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Get a list of all orders and the customer name associated with each order:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table (table1) and the matched rows from the right table (table2). If there is no match in the right table, the result is NULL in columns from the right table.

Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:

List all customers and any orders they might have. Customers without orders will still appear.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table (table2) and the matched rows from the left table (table1). If there is no match in the left table, the result is NULL in columns from the left table.

Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example:

List all orders and the customer name associated. If an order somehow didn't have a valid customer ID (unlikely but possible), it would still appear.

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

FULL JOIN (or FULL OUTER JOIN)

Returns all rows when there is a match in either the left or the right table. If there is no match, the missing side will have NULL values.

Syntax:

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Example:

Show all customers and all orders. If a customer has no orders, they will appear with NULL for OrderID. If an order has an invalid CustomerID, it will appear with NULL for CustomerName.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

CROSS JOIN

Returns the Cartesian product of the two tables. This means it combines every row from the first table with every row from the second table. This can result in a very large result set and is typically used for generating data or when a specific combination is needed, not for typical relational queries.

Syntax:

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Example:

Create all possible combinations of products and colors.

SELECT Products.ProductName, Colors.ColorName
FROM Products
CROSS JOIN Colors;

Self Joins

A self-join is a regular join, but the table is joined with itself. This is useful when you have a table that contains hierarchical data or relationships within the same table. You must use table aliases to distinguish between the two instances of the table.

Example:

Find all employees and their respective managers. Assume an Employees table with EmployeeID and ManagerID, where ManagerID refers to another EmployeeID in the same table.

SELECT
    e.EmployeeName AS Employee,
    m.EmployeeName AS Manager
FROM
    Employees e
LEFT JOIN
    Employees m ON e.ManagerID = m.EmployeeID;

Performance Considerations

Related Topics: