SQL JOINs: Combining Data from Multiple Tables

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 from multiple tables in a single query, creating a more comprehensive result.

INNER JOIN

INNER JOIN: Definition and Usage

An INNER JOIN returns only the rows where there is a match in both tables being joined. If a row in one table doesn't have a corresponding match in the other table (based on the join condition), that row is excluded from the results.

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

Example: Joining Orders and Customers

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

OrderID | CustomerName
-----------------------
(Order IDs and corresponding Customer Names will be displayed here)
      

This query returns only orders with matching customer information. Orders without a matching customer are not included.

Joining Multiple Tables

You can join more than two tables using multiple `INNER JOIN` clauses. This example adds shipper information.

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

OrderID | CustomerName | ShipperName
-----------------------------------
(Order IDs, Customer Names, and Shipper Names will be displayed here)
      

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

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

CustomerName | OrderID
-----------------------
(All customers will be listed; if a customer has no orders, OrderID will be NULL)
      

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

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
      

OrderID | LastName | FirstName
-------------------------------
(All employees will be listed; if an employee has no orders, OrderID will be NULL)
      

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

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

CustomerName             | OrderID
---------------------------------
(All customers and all orders will be listed; NULLs where there is no match)