SQL Joins: Combining Data from Multiple Tables

SQL joins are used to combine rows from two or more tables based on a related column. This allows you to retrieve data from multiple tables in a single query, creating a more comprehensive view of your information.

Understanding Joins

Let's illustrate with example tables. Suppose we have an 'Orders' table and a 'Customers' table:

Orders Table

Customers Table

Notice that the 'CustomerID' in the 'Orders' table links to the 'CustomerID' in the 'Customers' table. This common column is the key to joining the tables.

INNER JOIN Example

An INNER JOIN returns only the rows where there's a match in both tables. Here's how to combine 'Orders' and 'Customers' using an INNER JOIN:

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

OrderID | CustomerName                     | OrderDate
---------------------------------------------------
10308   | Ana Trujillo Emparedados y helados | 1996-09-18
(More rows would appear here based on matching CustomerIDs)
      

Types of SQL Joins

Different join types offer various ways to combine data:

• (INNER) JOIN: Returns rows only where there's a match in both tables.
• LEFT (OUTER) JOIN: Returns all rows from the left table and matching rows from the right table; if there's no match on the right, it shows NULL values for the right table's columns.
• RIGHT (OUTER) JOIN: Returns all rows from the right table and matching rows from the left table; NULL values are used for unmatched rows on the left.
• FULL (OUTER) JOIN: Returns all rows from both tables. If there is a match, the columns from both tables are combined; otherwise, NULL values are used for the missing columns.