Overview

Relational algebra provides a rigorous, theoretical foundation for a relational database. Within relational algebra, one of the most important operators is the join operator which allows you to combine data from multiple relations (tables, in the relational database world).

In order to be a productive relational database (e.g. Oracle, SQL Server, mySQL, Postgres) programmer, you need to thoroughly master the use of the join operator.

Let’s take a closer look at the different types of join operators.

Data sets

Eastern states

In our example, we are going to use two data sets (tables, in the relational database world). The first is a list of all United States states that are east of the Mississippi River. It looks like this:

Volkswagen dealers

The second data set is a list of all Volkswagen dealers across the United States. It looks like this:

Left join

A left join from state to dealer will return one row for each state/dealer pair for dealers in the given states. States without any dealers (e.g. Washington, DC) will be included. For example:

1
2
3
4
5     select s.code, s.name as "state_name", d.code as dealer_code, d.name, d.city
       from blog.state s
  left join blog.dealer d
         on ( s.code = d.state_code )
   order by s.code, d.name;

The results of the left join look like this:

Right join

A right join from state to dealer will return one row for each dealer, along with the state code and name for dealers in the given states. Dealers without a matching state will be included. For example:

1
2
3
4
5     select s.code, s.name as "state_name", d.code as dealer_code, d.name, d.city
       from blog.state s
 right join blog.dealer d
         on ( s.code = d.state_code )
   order by s.code, d.name;

The results of the right join look like this:

Inner join

An inner join from state to dealer will return one row for each state/dealer pair for dealers in the given states. States without any dealers (e.g. Washington, DC) will be excluded. For example:

1
2
3
4
5     select s.code, s.name as "state_name", d.code as dealer_code, d.name, d.city
       from blog.state s
 inner join blog.dealer d
         on ( s.code = d.state_code )
   order by s.code, d.name;

The results of the inner join look like this:

Left excluding join

A left excluding join from state to dealer will return one row for each state that has no associated dealers. For example:

1
2
3
4
5
6         select s.code, s.name as "state_name", d.code as dealer_code, d.name, d.city
           from blog.state s
      left join blog.dealer d
             on ( s.code = d.state_code )
          where d.state_code is null 
       order by s.code, d.name;

The results of the left excluding join look like this:

Right excluding join

A right excluding join from state to dealer will return one row for each dealer that has no associated state record. For example:

1
2
3
4
5
6     select s.code, s.name as "state_name", d.code as dealer_code, d.name, d.city, d.state_code
       from blog.state s
 right join blog.dealer d
         on ( s.code = d.state_code )
	  where s.code is null
   order by s.code, d.name;

The results of the right excluding join look like this:

Full outer join

Conceptually, a full outer join is a combination of both a left join and a right join. Specifically, a full outer join from state to dealer will return: one row for each state/dealer pair for dealers in the given states. We will include:

States without any dealers (e.g. Washington, DC)
Dealers without any state (e.g. Roger Jobs Volkswagen, Bellingham, WA)

For example:

1
2
3
4
5          select s.code, s.name as "state_name", d.code as dealer_code, d.name, d.city, d.state_code
            from blog.state s
 full outer join blog.dealer d
              on ( s.code = d.state_code )
        order by s.code, d.name;

The results of the full outer join look like this:

Outer excluding join

Conceptually, an outer excluding join is a combination of a left excluding join and a right excluding join. An outer excluding join from state to dealer will return:

One row for each state that has no associated dealers (e.g. Washington, DC)
0ne row for each dealer that has no associated state record (e.g. Roger Jobs Volkswagen, Bellingham, WA)

For example:

1
2
3
4
5
6          select s.code, s.name as "state_name", d.code as dealer_code, d.name, d.city, d.state_code
            from blog.state s
 full outer join blog.dealer d
              on ( s.code = d.state_code )
		   where ( s.code is null or d.state_code is null )
        order by s.code, d.name;

The results of the outer excluding join look like this:

Learn more about joins here.