Question

Give a brief explanation of how the JOIN operator is used, including sample syntax. Also explain how inner joins differ from outer joins and give an example of how their results might be different. Provide basic references.

Answer 1

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the "Orders" table:

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Then, look at a selection from the "Customers" table:

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

and it will produce something like this:

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996

A standard join in SQL is implicitly an inner join. This means that only records that can be matched on both sides of the join will be included in the result set.

Here is an example of that:

> select * from table_a join table_b on table_a.letter = table_b.letter;
 name  | letter |  name  | letter
-------+--------+--------+--------
 Brian | A      | Dunn   | A
 Derek | C      | Parker | C

That same query could be written with inner join.

We can alternatively write an outer join which will ensure that the subject of the join has all of it's rows included even if there isn't a matching row from the other table.

So, which table is the subject of the join? If it is a left outer join, then it is the table on the left of the join. If it is a right outer join, then it is the table on the right of the join.

Here is a left outer join:

select * from table_a left outer join table_b on table_a.letter = table_b.letter;
  name  | letter |  name  | letter
--------+--------+--------+--------
 Brian  | A      | Dunn   | A
 Thomas | B      | NULL   | NULL
 Derek  | C      | Parker | C

And here is a right outer join:

select * from table_a right outer join table_b on table_a.letter = table_b.letter;
 name  | letter |  name  | letter
-------+--------+--------+--------
 Brian | A      | Dunn   | A
 Derek | C      | Parker | C
 NULL  | NULL   | Worth  | D

Notice the difference in where full result sets come from and where null values have to be filled in depending on the left and right keywords.

For reference, here are the constituent tables:

> select * from table_a;
  name  | letter
--------+--------
 Brian  | A
 Thomas | B
 Derek  | C

> select * from table_b;
  name  | letter
--------+--------
 Dunn   | A
 Parker | C
 Worth  | D