SQL joins made easy

Details: Category: Database; Also available:; Created: 05 November 2011; Last Updated: 06 June 2022; Hits: 13709

SQL is not very easy to learn, in particular joins of multiiple tables are difficult to understand at a first glance. On the next pages it's explained for sql beginners how SQL joins work. Venn diagrams are used to explain how the tables are combined. In addition sample tables with sample data and sample joins are used to explain in detail the query input data results. SQL files to create the sample data and execute the queries can be downloaded. The samples are for mySQL and DB2.

If you want to see more complex sql joins in action visit Learning SQL by example for sql join examples and other SQL funktions as views, count, max, subselect and other.

sqj joins better because there exist similar operations.So Venn diagrams are used in the following paragraphs to help to explain the common SQL operations.

There basically exist four different types of sql join:

1) inner join

2) left/right outer join

3) full outer join

4) carthesian product

There exist two other joins which also have an equivalent Venn diagramm which helps to understand joins better:

5) Left outer join where null

6) Full outer joinwhere null

The following Venn diagrams use two tables TableA and TableB. They have rows with names of persons and other information. The join is executed on the names every time, i.e. all rows which have the same name are joined.

The examples used below use TableA and TableB and have following columns:

A key called id, a column name and a column nation which reflects the nationality of the person and a column table, which either is A or B if the row is stored in TableA or TableB.

Tabelle A:

| 2 | Elizabeth | England | A |

| 3 | Paul | Spain | A |

| 4 | Julia | Denmark | A |

+----+-----------+---------+-------+

Tabelle B:

mysql> select * from TableB;
+----+--------+--------+-------+
| id | name   | nation | table |
+----+--------+--------+-------+
| 1 | Marc   | China | B     |
| 2 | Albert | France | B     |
| 3 | Peter | Japan | B     |
| 4 | Paul   | Poland | B     |
+----+--------+--------+-------+

1) Inner join

An inner join combines all rows from TableA and TableB where the columns for the join are identical.

Example:

mysql> select * from TableA inner join TableB on TableA.name = TableB.name;
+----+--------+---------+-------+----+--------+--------+-------+
| id | name   | nation | table | id | name   | nation | table |
+----+--------+---------+-------+----+--------+--------+-------+
| 1 | Albert | Germany | A     | 2 | Albert | France | B     |
| 3 | Paul   | Spain   | A     | 4 | Paul   | Poland | B     |
+----+--------+---------+-------+----+--------+--------+-------+

The result table has combined the rows from TableA and TableB where the name is identical, i.e. Albert and Paul.

2) Left outer join

A left outer join combines all rows from TableA and TableB where the join columns are identical and all rows from the left table TableA which don't have any identical columns in TableB.

Example:

The result table has rows where the names are identical in TableA and TableB , e.i. Albert und Paul. There are also rows from Tabelle A with no match in TableB (Elizabeth und Julia). These rows have NULL values in the columns which come from TableB.

3) Full outer join

A full outer join combines all rows from TableA and TableB.

Example mySQL: (Because mysql has no full outer join the full outer join is done as the union of the left and right outer join of both tables, which creates the same result)

mysql> select * from TableA left join TableB on TableA.name = TableB.name
-> UNION
-> select * from TableA right join TableB on TableA.name = TableB.name;

Example DB2:

select * from TableA full join TableB on TableA.name = TableB.name;

The result table now has rows where the names of TableA and TableB is identical, i.e.. Albert and Paul. In addition all rows from TableA with no match (Elizabeth und Julia) and rows from TableB with no match (Marc und Peter). They have NULL values in the columns of the opposite table. Pls note that the union of the left and right join in general duplicates the rows hwihc have identical names (Paul and Albert) but the union operation removes these duplicates. The following example doesn#t remove the duplicates and the combination of Paul and Albert occurs twice.

4) Cross join - Carthesian product

If there is no join condition a carthesian product is created. The result table contains all rows from TableA combined with all rows from TableB. That way the number of rows in the result table is (number of rows of TabelleA) * (number of rows TabelleB). This can be a huge number of rows if the joined tables have a lot of rows. This join will be very time and memory consuming and should be avoided. Nevertheless this also can happen by error if the on conditions are wrong.

Example:

mysql> select * from TableA join TableB;
+----+-----------+---------+-------+----+--------+--------+-------+
| id | name      | nation | table | id | name   | nation | table |
+----+-----------+---------+-------+----+--------+--------+-------+
| 1 | Albert    | Germany | A     | 1 | Marc   | China | B     |
| 2 | Elizabeth | England | A     | 1 | Marc   | China | B     |
| 3 | Paul      | Spain   | A     | 1 | Marc   | China | B     |
| 4 | Julia     | Denmark | A     | 1 | Marc   | China | B     |
| 1 | Albert    | Germany | A     | 2 | Albert | France | B     |
| 2 | Elizabeth | England | A     | 2 | Albert | France | B     |
| 3 | Paul      | Spain   | A     | 2 | Albert | France | B     |
| 4 | Julia     | Denmark | A     | 2 | Albert | France | B     |
| 1 | Albert    | Germany | A     | 3 | Peter | Japan | B     |
| 2 | Elizabeth | England | A     | 3 | Peter | Japan | B     |
| 3 | Paul      | Spain   | A     | 3 | Peter | Japan | B     |
| 4 | Julia     | Denmark | A     | 3 | Peter | Japan | B     |
| 1 | Albert    | Germany | A     | 4 | Paul   | Poland | B     |
| 2 | Elizabeth | England | A     | 4 | Paul   | Poland | B     |
| 3 | Paul      | Spain   | A     | 4 | Paul   | Poland | B     |
| 4 | Julia     | Denmark | A     | 4 | Paul   | Poland | B     |
+----+-----------+---------+-------+----+--------+--------+-------+

All rows from TableA are combined with all elements from TableB and no columns are NULL.

There doesn't exists an equivalent Venn diagram for this.

5) Left outer join where null

This join combines all rows of the left TableA which don't have a peer in the other table. It's the same as a left outer join reduced by the elements in an inner join.

Example:

The result table has only rows which don't have teh same name in TableB. Because a join combines both tables the columns from TableB are NULL for every row.

6) Full outer join where null

This join combines all rows which don't have a peer element in the other table. It's the same as a full outer join reduced by the elements in an inner join.

Example:

Both tables are combined and there is no row which has a matching row in the other table. Therefore all rows either have NULL in the columns of the first table or second table.