The power of questions - JOIN and GROUP BY

Data without questions is a pile of dead scrolls. The true power of the Library awakens only when we link shelves and count patterns. Let us recall the two pillars of this art.

JOIN - linking shelves

JOIN

matches rows from two tables on a shared column. Most often we join a table by its foreign key. We write the matching condition after the

ON

keyword:

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

• INNER JOIN (the default

  JOIN

  ) - returns only matched rows.
• LEFT JOIN - keeps ALL rows from the left table, even if they have no match on the right (empty fields are filled with

  NULL

  ).

GROUP BY - counting in groups

GROUP BY

merges rows with the same value into one group, and aggregate functions compute something for each group:

1SELECT czytelnik_id, COUNT(*) AS liczba
2FROM wypozyczenia
3GROUP BY czytelnik_id;

This query answers: "How many times did each reader borrow something?". The most common functions are

COUNT()

,

SUM()

,

AVG()

,

MIN()

,

MAX()

.

JOIN + GROUP BY together

Combined, they produce reports - the heart of every library:

1SELECT c.imie, COUNT(*) AS liczba
2FROM czytelnicy c
3JOIN wypozyczenia w ON c.id = w.czytelnik_id
4GROUP BY c.id;

First we link the shelves, then we group and count. This is how knowledge is born from raw data!