Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	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:

using the JOIN ... ON (...) clause makes more explicit what's going on.
and JOIN conditions are now directly expressed within the ON (...) term:
this way the query statement is better structured and more readable.
anyway, all this simply is syntactic sugar:
there is no difference at all between the above two queries in functional terms.

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:

we simply introduced a further ON (... AND lc.population < 100000) clause, so to exclude any small populated Local Council.

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:

this time we've used a LEFT JOIN clause:
and the result-set now looks very different from the previous one.
a plain JOIN clause will include into the result-set only rows for which both the left-sided and the right-sided terms has been positively resolved.
but the most sophisticated LEFT JOIN clause will include into the result-set any row having an unresolved right-sided term:
and in this case any right-sided member assumes a NULL value.

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

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

	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.