The Art of PostgreSQL/

...

/

Joins and Restrictions

Joins and Restrictions

Learn about two major database concepts: joins and restricting the output.

We'll cover the following...

Understanding joins

We could spend time here with detailed explanations of every kind of join operation: inner join, left and right outer join, cross join, full outer join, lateral join, and more. It just so happens that the PostgreSQL documentation covering the FROM clause does that very well, so please read it carefully so we can instead focus on more interesting and advanced examples.

Now that we know how to easily fetch the winner of a race, it’s possible to also display all the races from a quarter with their winner:

Press + to interact
\set beginning '2017-04-01'
\set months 3
select date, 
       name, 
       drivers.surname as winner
  from races
       left join results
              on results.raceid = races.raceid
             and results.position = 1
       left join drivers using(driverid)
 where date >= date :'beginning'
   and date <  date :'beginning'
              + :months * interval '1 month';

We get the following result, where we lack data for the most recent race but still display it:

    date    │         name          │  winner  
════════════╪═══════════════════════╪══════════
 2017-04-09 │ Chinese Grand Prix    │ Hamilton
 2017-04-16 │ Bahrain Grand Prix    │ Vettel
 2017-04-30 │ Russian Grand Prix    │ Bottas
 2017-05-14 │ Spanish Grand Prix    │ Hamilton
 2017-05-28 │ Monaco Grand Prix     │ Vettel
 2017-06-11 │ Canadian Grand Prix   │ Hamilton
 2017-06-25 │ Azerbaijan Grand Prix │ ¤
(7 rows)

The reason why we’re using a left join this time is so that we keep every race from the quarters and display extra information only when we have it. The left join semantics is to keep the whole result set of the ...