SQL Join Syntax

I have always found the different types of joins a bit confusing, but now thanks to a little experimenting I think I have a handle on it. This is geared toward Oracle, but most of this is ANSI SQL, so should work in other databases as well. Some of these features may not be available in Oracle pre-9i.

Assume these two tables:

SELECT * FROM faculty;

ID	FIRST_NAME	LAST_NAME
1	Jon	Emmons
2	Zach	Tirrell
3	Evelyn	Stiller

SELECT * FROM class;

CLASS_ID	CATALOG_NUMBER	INSTRUCTOR_ID
1	CS3600	1
2	CS3020	2
3	CS2000
4	CS1100	1

Inner Join

The most common (and simple) join. This will select the rows which satisfy the join condition; however if a row exists in one table but does not have a counterpart to fulfill the join condition those rows (from either table) will be ignored.

SELECT *
FROM faculty, class
WHERE faculty.id = class.instructor_id;

ID	FIRST_NAME	LAST_NAME	CLASS_ID	CATALOG_NUMBER	INSTRUCTOR_ID
1	Jon	Emmons	1	CS3600	1
2	Zach	Tirrell	2	CS3020	2
1	Jon	Emmons	4	CS1100	1

Here we see the three rows where faculty.id had a match to class.instructor_id.

Left Join

A left join, a.k.a. left outer join will return all the row combinations which meet the join condition plus any rows from the first table which do not meet the guard condition.

SELECT *
FROM faculty LEFT JOIN class
ON faculty.id=class.instructor_id;

ID	FIRST_NAME	LAST_NAME	CLASS_ID	CATALOG_NUMBER	INSTRUCTOR_ID
1	Jon	Emmons	1	CS3600	1
1	Jon	Emmons	4	CS1100	1
2	Zach	Tirrell	2	CS3020	2
3	Evelyn	Stiller

Now we see all the results we saw in the inner join, but we additionally see the row from faculty (id 3, Evelyn Stiller) which has no corresponding row in class.

Right Join

Also referred to as right outer join, this will show all row combinations which meet the join criteria, but will additionally show any rows from the second table which do not have counterparts in the first.

SELECT *
FROM faculty RIGHT JOIN class
ON faculty.id=class.instructor_id;

ID	FIRST_NAME	LAST_NAME	CLASS_ID	CATALOG_NUMBER	INSTRUCTOR_ID
1	Jon	Emmons	1	CS3600	1
2	Zach	Tirrell	2	CS3020	2
			3	CS2000
1	Jon	Emmons	4	CS1100	1

Now we see rows from the class table which do not have a faculty counterpart.

Outer Join

The outer join, or full outer join can be thought of a left join and right join. Rows which meet the join condition will of course be displayed, additionally, rows from both the first and second table referenced will be displayed.

SELECT *
FROM faculty FULL OUTER JOIN class
ON faculty.id = class.instructor_id;

ID	FIRST_NAME	LAST_NAME	CLASS_ID	CATALOG_NUMBER	INSTRUCTOR_ID
1	Jon	Emmons	1	CS3600	1
1	Jon	Emmons	4	CS1100	1
2	Zach	Tirrell	2	CS3020	2
3	Evelyn	Stiller
			3	CS2000

Now we see all the rows from both the tables, joined where the join condition is met, or with corresponding null values where the join condition failed.

oracle, sql, dba, database administration, database development