LEFT JOIN - every scroll from the left shelf

INNER JOIN

shows only complete pairs. Sometimes, however, we want to see all rows from one shelf, even if they have no pair in the other. That is what LEFT JOIN is for.

What LEFT JOIN does

A LEFT JOIN B

returns:

• all rows from the left table (

  A

  ),
• matched rows from the right table (

  B

  ),
• and where there is no match, it fills the columns from

  B

  with the value NULL (emptiness).

1SELECT c.nazwisko, w.data_wypozyczenia
2FROM czytelnicy c
3LEFT JOIN wypozyczenia w ON w.czytelnik_id = c.id;

This means: "Show every reader, and next to them the date of their loan - and if they have never borrowed anything, leave an empty space (NULL)".

| nazwisko | data_wypozyczenia | |----------|-------------------| | z Aleksandrii | 0250-03-01 | | Nowicjusz | NULL |

When to use it

LEFT JOIN

is ideal when we do not want to lose rows without a pair. The classic example: a list of readers who borrowed nothing. With

INNER JOIN

such readers would disappear - because they have no row in

wypozyczenia

.

The absence filter: IS NULL

Since missing matches produce NULL, we can filter them out. To find readers without a single loan, we look for empty cross-references:

1SELECT c.nazwisko
2FROM czytelnicy c
3LEFT JOIN wypozyczenia w ON w.czytelnik_id = c.id
4WHERE w.id IS NULL;

This is a powerful combination:

LEFT JOIN

keeps everyone, and

WHERE w.id IS NULL

leaves only those without a pair. The same way we find empty categories or authors without books.