Combine Tables Using JOIN

Foreign keys create relationships between tables, but the JOIN clause allows us to retrieve data from these related tables in a single query. This lesson explores how JOIN works, its syntax, and common types of JOIN operations.

What is a JOIN?

A JOIN operation retrieves rows from multiple tables based on a specified relationship. By combining tables, you can access data from different entities in a cohesive manner.

Example Tables

Consider the following tables:

puppies table:

id   name       age_yrs   breed                weight_lbs   microchipped   owner_id
1    Cooper     1.0       Miniature Schnauzer   18           1              1
2    Indie      0.5       Yorkshire Terrier     13           1              2
3    Kota       0.7       Australian Shepherd   26           0              5
4    Zoe        0.8       Korean Jindo          32           1              2
5    Charley    1.5       Basset Hound          25           0              3
6    Ladybird   0.6       Labradoodle           20           1              3
7    Callie     0.9       Corgi                 16           0              4
8    Jaxson     0.4       Beagle                19           1              1
9    Leinni     1.0       Miniature Schnauzer   25           1              1
10   Max        1.6       German Shepherd       65           0              6
    

owners table:

id   first_name   last_name
1    Jamie         Anderson
2    Linda         Long
3    Sadie         Kafka
4    Jake          Laughlin
5    Laney         Rous
6    Alexander     Lee
    

The owner_id column in the puppies table is a foreign key referencing the id column in the owners table.

Performing a JOIN

To combine these tables and retrieve the names of puppies alongside their owners' first names, use the following query:

SELECT puppies.name, owners.first_name 
  FROM puppies 
  JOIN owners ON (puppies.owner_id = owners.id);

Result:

name       first_name
Cooper     Jamie
Indie      Linda
Kota       Laney
Zoe        Linda
Charley    Sadie
Ladybird   Sadie
Callie     Jake
Jaxson     Jamie
Leinni     Jamie
Max        Alexander
    

JOIN Syntax

SELECT [columns] 
  FROM [table1] 
  JOIN [table2] 
  ON [column-table1 = column-table2];

Explanation:

• [table1]: The first table in the query.
• [table2]: The second table to join with the first.
• [column-table1] and [column-table2]: The columns used to create the relationship.

Types of JOIN

There are different types of JOIN operations. Each retrieves data differently based on the relationship between the tables.

1. Inner Join

Returns rows where there is a match in both tables. This is the default JOIN type.

2. Left Join

Returns all rows from the left table and the matching rows from the right table. Rows with no match in the right table have NULL values.

3. Right Join

Returns all rows from the right table and the matching rows from the left table. Rows with no match in the left table have NULL values.

4. Full Outer Join

Returns all rows from both tables, with NULL values for missing matches on either side.

Choosing the Right JOIN

Inner Join is the most commonly used JOIN because it only retrieves rows with matches in both tables, making it efficient for many use cases. For less common JOIN types, consult your SQL implementation's documentation.

What You Learned

In this lesson, you learned:

• How to use the JOIN clause to combine tables.
• The syntax for creating JOIN queries.
• The differences between Inner, Left, Right, and Full Outer Joins.

Real-World Applications

JOINs are crucial for:

• Customer management: Linking customer data with their purchase history.
• Inventory systems: Associating products with suppliers or categories.
• Reporting: Combining data from multiple sources for analysis.

Mastering JOIN operations enables you to build efficient queries for complex relationships, making it a key skill in database management.