Querying multiple tables with joins

Mastering JOINS is key to exploiting the true power of relational databases. The JOIN clause combines data across multiple tables using logical conditions, enabling more complex querying for business insights.

INNER JOIN

The INNER JOIN selects matching rows from two tables. For example, to match customer and order data:

SELECT c.Name, o.OrderTotal
FROM Customers c
INNER JOIN Orders o ON c.Id = o.CustomerId

This would retrieve customer name and order total for every customer that has orders. The ON condition matches the tables based on their relationship.

The INNER keyword is optional - JOIN alone implies an inner join.

LEFT/RIGHT OUTER JOIN

OUTER JOINs return all rows from one table plus matched rows from the other table. This is useful when one table contains additional records missing from the other side.

A LEFT JOIN returns all rows from the left table:

SELECT c.Name, o.OrderTotal
FROM Customers c
LEFT JOIN Orders o ON c.Id = o.CustomerId

Now every customer is returned, even those without orders yet. RIGHT JOIN reverses the logic based on the right table.

FULL OUTER JOIN

The FULL OUTER JOIN combines the logic from both left and right joins. Every row from both tables are returned irrespective of matches.

Rows with data in one table but not the other have NULLs for mismatched columns.

SELECT Employees.Name, Employees.Department, Sales.SalesAmount
FROM Employees
FULL OUTER JOIN Sales
  ON Employees.EmployeeID = Sales.EmployeeID

SELF JOINS

A table can JOIN to itself to query hierarchical structures, like employees and their managers within the same table:

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id

ALIASING TABLE NAMES

Table aliases shorten queries and avoid self join collisions:

SELECT o.Product, c.Name AS Customer
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id

Here o and c aliases the Order and Customer tables respectively.

FILTERING JOINS

The ON clause filters which rows match across tables, while WHERE filters after joining:

SELECT *
FROM OrderDetails o
JOIN Products p ON o.ProductId = p.Id
WHERE p.Discontinued = 0

This maintains relationships between all orders and current products only. The JOIN + WHERE combo powerfully filters multi-table data.

Learning to traverse relationships through SQL JOINS enables consolidating information from normalized tables into a single resultset! Let me know if you have any other JOIN questions.