Self-join and INNER versus LEFT

Before we leave the bridge, you will learn two subtleties of joining shelves: the difference between

INNER

and

LEFT

in practice, and a trick called the self-join.

INNER versus LEFT - on one example

Imagine a reader who has just registered and borrowed nothing.

• INNER JOIN

  czytelnicy

  with

  wypozyczenia

  - such a reader disappears, because there is no pair.
• LEFT JOIN

  czytelnicy

  with

  wypozyczenia

  - such a reader stays, and the loan date is NULL.

| | INNER JOIN | LEFT JOIN | |---|------------|-----------| | Rows without a pair | disappear | stay (with NULL) | | When to use | only complete pairs | all rows from the left |

This is the only - but truly fundamental - difference between these two bridges.

Self-join - a shelf linked to itself

Sometimes we join a table with itself (self-join). We then give it two different aliases, as if they were two separate shelves:

1SELECT a1.nazwisko, a2.nazwisko
2FROM autorzy a1
3INNER JOIN autorzy a2 ON a1.kraj = a2.kraj;

This query pairs up authors from the same country. The aliases

a1

and

a2

are two "copies" of the same

autorzy

table - thanks to them MySQL knows which row is meant in the

ON

condition and in

SELECT

.

The self-join is useful wherever we compare rows within a single table - for example authors from the same country or books published in the same year. It is an advanced but powerful bridge trick.