Joining Tables

Lesson 7 of 8 · Database Fundamentals · Beginner

Most useful database queries involve more than one table. A join combines rows from two tables based on a matching column — usually a foreign key relationship. This lesson explains why joins are needed and how the two most common types work.

Why Joins Are Needed

In a well-designed database, information is spread across multiple tables. The programs table stores program details, but the location name is in the locations table. To see a program's name alongside its location name, you need to join the two tables.

The Basic Join Syntax

A join specifies which tables to combine and which columns to match on. Table aliases (short names like p for programs) make queries more readable:

SELECT
    programs.program_name,
    locations.location_name
FROM programs
INNER JOIN locations
    ON programs.location_id = locations.location_id;

This returns one row for each program, with the matching location name alongside it. Programs that have no matching location are excluded.

Inner Join

An INNER JOIN returns only rows where a match exists in both tables. If a program has a location_id that does not exist in the locations table, that program is excluded from the results. This is the most common join type.

Left Join

A LEFT JOIN returns all rows from the left table (the one listed first), plus matching rows from the right table. If there is no match, the right-table columns appear as null:

SELECT
    locations.location_name,
    programs.program_name
FROM locations
LEFT JOIN programs
    ON locations.location_id = programs.location_id;

This returns every location, even those with no programs. For locations without programs, program_name will be null. This is useful for finding unmatched records — locations that have no programs assigned.

One-to-Many Effects on Row Counts

When you join a "one" side to a "many" side, the result has one row per "many" record. If a location has five programs, the join produces five rows for that location — one for each program. This is expected behaviour, but it can surprise beginners who expect one row per location.

Validating Join Results

Before trusting a join result, check the row count. If you joined two tables and expected 50 rows but got 200, a one-to-many relationship may have multiplied rows unexpectedly. Count the rows in each table before joining and compare to the result.

Also check for null values in the joined columns — they indicate unmatched records that may need investigation.

Key Takeaways

• Joins combine rows from two tables based on a matching column.
• INNER JOIN returns only rows with matches in both tables.
• LEFT JOIN returns all rows from the left table, with nulls where no match exists.
• One-to-many joins multiply rows — check row counts before and after.
• Join on stable identifiers, not on text fields that may vary.