Course 19: Joins in SQL

Oct 3, 2021
Course 19: Joins in SQL

The joins we have used so far are called inner joins . If we call them internal , it is because there must be external ones , and this is the case. Before we look at them in detail, let’s take a look at the limitations of inner joins.

4.1. Inner join

All of the joins we’ve written so far are inner joins. As we have seen, we can nest the inner joins, and join in this way as many tables as we want. Strictly speaking, rather than using joinin our queries, we should have used inner jointo show the “inner” character of these joins. However, when the keyword inneris omitted, the join is internal.

Inner joins unfortunately do not answer all the cases that we encounter. Consider the following example.

We are interested in ships that deliver goods. A first table groups the boats, and a second stores the deliveries of each boat. A boat can deliver several times, so the relationship between the boat table and the delivery table is of type 1:p.

First, let’s see the data structure we are going to use.

Example 65. Ship and Shipment Data Structure

- boat 
 table create   table Boats (
   id   int   primary   key ,
   ship_name   varchar (32)

 - deliveries 
 table create   table Deliveries (
   id   int   primary   key ,
   id_bateau   int ,int 

 - a delivery must reference a boat 
 alter   table Deliveries
  add   constraint FK_livraisons_bateaux
     foreign   key Deliveries (boat_id)   references Boats (id);

Here is now the content of our tables.

Table 13. Boat table

ID Boat
1 Astonished
2 Altaïr
3 Pearl
4 Amethyst

Here is the tonnage delivered for each delivery, for each boat.

Table 14. Deliveries table

Boat ID tonnage delivered
1 120
1 110
2 220
3 190

We now want to draw up a table of the tonnage delivered by boat. We have all the elements to write this request.

Example 66. Tonnage delivered by vessel – 1

select boat_name, sum (tonnage)
  from Deliveries
     join Boats   on Deliveries.boat_id = Boats.id
  group   by boat_name
  order   by boat_name;

The result is as follows.

   -------------------- ------------
   Altaïr 220
   Jellyfish 230
   Pearl 190

We immediately notice that a boat is missing: the Amethyst. It was not selected by the query, simply because it did not deliver anything, and the predicate Livraisons.id_bateau = Bateaux.idis never true for Amethyst.

What we would have liked is for Amethyst to appear in the results table, with a value of 0 for the tonnage, which is not possible with inner joins. This is where outer joins come in.

4.2. Outer join

Outer joins make it possible to preserve one or the other part of the join that is performed. Let’s write an outer join for our delivered tonnage table.

Example 67. Tonnage delivered by vessel – 2

select boat_name, sum (tonnage)
  from Deliveries
     right   outer   join Boats   on Deliveries.boat_id = Boats.id
  group   by boat_name
  order   by boat_name;

The result this time around is as follows.

   -------------------- ------------
   Altaïr 220
   Jellyfish 230
   Pearl 190

Using the clause right outer joinrather than jointells the server that the table to the right of the join should be preserved in the result, that is, all of its rows should appear, even if they do not meet the join condition.

We can use the clause in the same way left outer join, to preserve the rows of the left table, or full outer jointo preserve the rows of the two tables.

4.3. Self-join

Note that it is perfectly possible to write the join of a table on itself. This is often what is used when one wishes to code hierarchical structures. We can take the example of our sailors, some of whom are captains, who have other sailors under their orders.

Example 68. Hierarchical structure

- our sailor table with the 
 create   table Sailors subordination link (
   id   int   primary   key ,
   name   varchar (32),
   captain_id   int 

 - a sailor must have a captain 
 alter   table Marins
  add   constraint FK_marins_capitaine
     foreign   key Marins (id_capitaine)   references Marins (id);
 - list of sailors with the name of their captain 
 select t1.name   as sailor_name, t2.name   as captain_name
  from Sailors   as t1
     join Sailors   as t2   on t1.captain_id = t2.id
  order   by sailor_name;

Let’s take a look at the handwriting of selectwhich displays the table of all sailors who have a captain, and their captain. The problem here is that the table Marinsis referenced twice in the clause from: on either side of the join. For this to work, you must use an alias: Marins as t1and Marins as t2. In this way, we can differentiate the table which is to the right of the join, from that which is to the left. It will be noted that the list of fields specifies in which table one chooses the field which one wants.

If we had wanted the full list of sailors, whether they had a captain or not, we would simply have replaced the joinby left outer join.

Leave a Reply

Your email address will not be published. Required fields are marked *