Joins

A join takes two tables and stitches them into one, based on some matching condition. Think of it as a Cartesian product (every row paired with every row) that’s then filtered down to only the pairs that actually make sense together.

Every join (except cross join) is a join type plus a join condition:

Join typewhich rows are kept

Join conditionwhich rows match

Join types

innermatching rows only

left outer+ unmatched left rows

right outer+ unmatched right rows

full outer+ unmatched rows of both

Join conditions

naturalall same-named columns

using (A, ...)only the listed columns

on <predicate>any condition

Sample Data

The joins on this page use small relations from the three course datasets. Run each block to see the rows that will be joined.

University: the courses offered in Fall 2009 and the courses offered in Spring 2008. Run this block once to create them as two views, then look at them:

create or replace view fall_2009 as
    select distinct s.course_id, c.title
    from section as s join course as c on s.course_id = c.course_id
    where s.semester = 'Fall' and s.year = 2009;

create or replace view spring_2008 as
    select distinct course_id, building
    from section
    where semester = 'Spring' and year = 2008;

select * from fall_2009;     -- 7 courses
select * from spring_2008;   -- 9 courses

Only course 237 is in both: 6 Fall courses have no Spring match, and 8 Spring courses have no Fall match. This mismatch is exactly what different join types handle differently.

LIS: faculty, and the library members who are faculty:

select id, faculty_fname, faculty_lname from faculty;
select member_no, id from members where member_class = 'Faculty';

FLIS: the teams and the referees:

select team_id, name from teams;
select referee_id, name from referees;

Cross Join

Returns the full Cartesian product — every row of one table paired with every row of the other, no filtering at all.

-- Explicit
select * from fall_2009 cross join spring_2008;

-- Implicit (comma syntax)
select * from fall_2009, spring_2008;

With 7 Fall courses and 9 Spring courses, this produces 63 rows — almost all of them meaningless pairings.

The same on another dataset: every team paired with every referee (6 × 7 = 42 rows):

select count(*)
from teams cross join referees;

Inner Join

Keeps only the rows where the join condition is satisfied on both sides.

Join Type	Condition	Result
Equi-Join	Explicit equality condition (`on a.x = b.x`)	Both `x` columns kept
Natural Join	Equality assumed on same-named columns	Duplicate column auto-dropped

-- Equi-join (course_id appears twice)
select * from fall_2009 inner join spring_2008
on fall_2009.course_id = spring_2008.course_id;

-- Natural join (same row, but course_id appears once)
select * from fall_2009 natural join spring_2008;

Only course 237 survives. The other Fall and Spring courses all disappear — this is the information loss outer joins solve.

course_id	title	building	what happens
237	Surfing	Power	in both: kept
105	Image Processing	–	no Spring match: dropped
158	–	Taylor	no Fall match: dropped

Outer Join

Outer joins keep the inner join result plus the unmatched rows from one or both sides, filling missing columns with null.

1 · inner join

matching rows

→

2 · add unmatched

from the left, right or both

→

3 · fill

missing columns with null

Left Outer Join

Keeps all rows from the left table, even unmatched ones.

select * from fall_2009 natural left outer join spring_2008;

All 7 Fall courses are kept; the 6 with no Spring match get null for building.

course_id	title	building
237	Surfing	Power
105	Image Processing	null
242	Rock and Roll	null
…

The same on another dataset: every faculty member with their library member number. The faculty member who is not a library member is kept, with null:

select f.faculty_fname, f.faculty_lname, m.member_no
from faculty as f left outer join members as m
on m.id = f.id;

Right Outer Join

Keeps all rows from the right table, even unmatched ones.

select * from fall_2009 natural right outer join spring_2008;

All 9 Spring courses are kept; the 8 with no Fall match get null for title.

course_id	title	building
237	Surfing	Power
158	null	Taylor
345	null	Taylor
…

The same on another dataset: every department with its instructors, including the departments that have none:

select dept_name, id, name
from instructor natural right outer join department;

Full Outer Join

Keeps everything from both sides — the union of left and right outer joins.

select * from fall_2009 natural full outer join spring_2008;

1 matched course + 6 Fall-only + 8 Spring-only = 15 rows.

course_id	title	building	came from
237	Surfing	Power	both
105	Image Processing	null	Fall only
158	null	Taylor	Spring only
…

Using Explicit Conditions

Joins can also use on (any condition) or using (shared column names), instead of natural:

-- Explicit condition
select * from fall_2009 left outer join spring_2008
on fall_2009.course_id = spring_2008.course_id;

-- Shared column shorthand
select * from fall_2009 full outer join spring_2008
using (course_id);

Key idea

on works with any condition (even inequality!). natural and using both rely on matching column names — using lets you name exactly which shared column(s) to join on, while natural matches on all identically-named columns.

Quick Reference: Join Types

Join	Unmatched left rows kept?	Unmatched right rows kept?
Cross Join	n/a (no matching)	n/a
Inner Join	✗	✗
Left Outer	✓	✗
Right Outer	✗	✓
Full Outer	✓	✓