Lesson 8

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.


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.


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.


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
  ON Employees.EmployeeID = Sales.EmployeeID


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


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.


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

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.

Retrieve all book titles with their corresponding authors' first and last name
Retrieve all authors first and last name and their books titles, including authors who have not written any books
Retrieve all books title and their authors first and last name, including books that do not have an associated author in the system
Retrieve a list of authors who share the same last name but are not the same individual
Tables used in this quizz:
No Data

©2024 SQLZap - Learn SQL Interactively