SpatiaLite logo

Recipe #3:
More about JOIN

2011 January 28

Previous Slide Table of Contents Next Slide

SQL supports another alternative syntax to represent JOIN ops.
More or less both implementations are strictly equivalent, so using the one or the other simply is matter of personal taster in the majority of cases.
Anyway, this second method supports some really interesting further feature that is otherwise unavailable.


SELECT lc.lc_id AS lc_id,
  lc.lc_name AS lc_name,
  lc.population AS population,
  c.county_id AS county_id,
  c.county_name AS county_name,
  c.car_plate_code AS car_plate_code,
  r.region_id AS region_id,
  r.region_name AS region_name
FROM local_councils AS lc,
  counties AS c,
  regions AS r
WHERE lc.county_id = c.county_id
  AND c.region_id = r.region_id;
You now feel a strong deja vu sensation: and that's more than appropriate, because you have already encountered this query in the previous example.

SELECT lc.lc_id AS lc_id,
  lc.lc_name AS lc_name,
  lc.population AS population,
  c.county_id AS county_id,
  c.county_name AS county_name,
  c.car_plate_code AS car_plate_code,
  r.region_id AS region_id,
  r.region_name AS region_name
FROM local_councils AS lc
JOIN counties AS c ON (
  lc.county_id = c.county_id)
JOIN regions AS r ON (
  c.region_id = r.region_id);

All right, this one is the same identical query rewritten accordingly to alternative syntax rules:

SELECT r.region_name AS region,
  c.county_name AS county,
  lc.lc_name AS local_council,
  lc.population AS population
FROM regions AS r
JOIN counties AS c ON (
  c.region_id = r.region_id)
JOIN local_councils AS lc ON (
  c.county_id = lc.county_id
  AND lc.population > 100000)
ORDER BY r.region_name,
  county_name;

region county local_council population
ABRUZZO PESCARA PESCARA 116286
CALABRIA REGGIO DI CALABRIA REGGIO DI CALABRIA 180353
CAMPANIA NAPOLI NAPOLI 1004500
CAMPANIA SALERNO SALERNO 138188
EMILIA-ROMAGNA BOLOGNA BOLOGNA 371217
... ... ... ...
There is nothing strange or new in this query:
SELECT r.region_name AS region,
  c.county_name AS county,
  lc.lc_name AS local_council,
  lc.population AS population
FROM regions AS r
JOIN counties AS c ON (
  c.region_id = r.region_id)
LEFT JOIN local_councils AS lc ON (
  c.county_id = lc.county_id
  AND lc.population > 100000)
ORDER BY r.region_name,
  county_name;

region county local_council population
ABRUZZO CHIETI NULL NULL
ABRUZZO L'AQUILA NULL NULL
ABRUZZO PESCARA PESCARA 116286
ABRUZZO TERAMO NULL NULL
BASILICATA MATERA NULL NULL
BASILICATA POTENZA NULL NULL
... ... ... ...
Apparently this query is the same as the latest one.
But a remarkable difference exists:
There is a striking difference between a plain JOIN and a LEFT JOIN.
Coming back to previous example, using a LEFT JOIN clause ensures that any Region and any County will now be inserted into the result-set, even the ones failing to satisfy the imposed population limit for Local Councils.

Previous Slide Table of Contents Next Slide

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.