As we discussed, data in relational databases is often distributed across multiple tables to maintain organization and reduce redundancy. For instance, you might have one table listing customer details and another table listing the orders placed by those customers. To analyze customer order patterns, you need a way to connect the information from both tables. SQL provides a powerful mechanism for this: JOIN operations.

A JOIN clause in SQL is used to combine rows from two or more tables based on a related column between them. Think of it as horizontally stitching together tables where the values in specific columns match up. This relationship is typically established using primary keys and foreign keys, which act as the links between tables, ensuring data integrity.

View of joining a Customers table and an Orders table using the CustomerID column.

While the concept seems straightforward, there are different ways to join tables depending on precisely which rows you want to include in your result set. SQL offers several types of JOIN operations to handle various scenarios:

Common SQL JOIN Types

INNER JOIN: This is the most common type of join. It returns only the rows where the join condition is met in both tables. If a customer has placed no orders, neither the customer nor any order information will appear in the result. Likewise, if an order references a CustomerID not present in the Customers table (which shouldn't happen with proper foreign key constraints), that order wouldn't be included. It finds the intersection of the two tables based on the linking column(s).
LEFT JOIN (or LEFT OUTER JOIN): This join returns all rows from the left table (the first table listed in the FROM clause) and the matched rows from the right table (the table named in the JOIN clause). If there's no match in the right table for a row in the left table, the result will still include the row from the left table, but with NULL values for all columns selected from the right table. This is useful when you want all information from the primary table (e.g., all customers) regardless of whether they have matching records in the secondary table (e.g., orders).
RIGHT JOIN (or RIGHT OUTER JOIN): This is the reverse of a LEFT JOIN. It returns all rows from the right table and the matched rows from the left table. If there's no match in the left table for a row in the right table, the result will show NULL values for the columns from the left table.
FULL OUTER JOIN: This join returns all rows when there is a match in either the left or the right table. It essentially combines the results of a LEFT JOIN and a RIGHT JOIN. Rows unique to either table are included, with NULL values filling in the columns from the table where no match was found.

While understanding the different types of joins is beneficial, the INNER JOIN is fundamental and widely used in data analysis for combining related data. This chapter will concentrate primarily on the INNER JOIN. In the following sections, we will look closely at the syntax for writing INNER JOIN clauses, how to specify the join condition, and how to apply this to combine data effectively from multiple tables.