JOIN: Odczytywanie Danych z Powiązanych Tabel

Witaj z powrotem.

W poprzedniej lekcji nauczyłeś się relacji.

Dowiedziałeś się:

• czym jest primary key;
• czym jest foreign key;
• dlaczego tabele powinny być połączone;
• jak działają relacje one-to-many;
• jak PostgreSQL chroni relacje.

Bardzo dobrze.

Teraz wiemy, jak łączyć tabele.

Ale nadal mamy jeden problem.

Kiedy robimy query dla produktów, widzimy coś takiego:

Laptop | category_id = 1
Mouse  | category_id = 1
Chair  | category_id = 2

Przydatne.

Ale niezbyt przyjazne.

Ludzie nie chcą czytać category IDs.

Ludzie chcą nazw.

Chcemy tego:

Laptop | Electronics
Mouse  | Electronics
Chair  | Furniture

Tutaj pomaga JOIN.

JOIN pozwala czytać dane z wielu powiązanych tabel w jednym query.

To jest moment, w którym relacyjne bazy danych zaczynają wyglądać naprawdę potężnie.

I trochę elegancko.

Jak SQL w marynarce.

Czego Się Nauczysz

W tej lekcji nauczysz się:

• co robi JOIN;
• dlaczego JOIN jest przydatny;
• jak używać INNER JOIN;
• jak łączyć tabele przez primary keys i foreign keys;
• jak wybierać kolumny z wielu tabel;
• jak używać aliasów tabel;
• jak unikać mylących nazw kolumn;
• jak używać LEFT JOIN;
• jaka jest różnica między INNER JOIN i LEFT JOIN;
• jak łączyć więcej niż dwie tabele;
• jakie są typowe błędy przy JOIN.

Na końcu tej lekcji będziesz umieć łączyć dane z powiązanych tabel.

To bardzo ważny krok.

Bez JOIN relacje są ukryte.

Z JOIN relacje stają się czytelne.

Baza danych przestaje pokazywać tajemnicze numerki.

I zaczyna pokazywać użyteczne informacje.

Bardzo miło.

Bardzo PostgreSQL.

Przygotuj Bazę Danych

Otwórz PostgreSQL:

sudo -iu postgres psql

Połącz się z bazą danych:

\c learning_postgresql

Jeśli nie masz tej bazy danych, utwórz ją:

CREATE DATABASE learning_postgresql;

Potem połącz się:

\c learning_postgresql

Teraz usuń stare tabele, jeśli istnieją:

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

Usuwamy najpierw products, bo zależy od categories.

PostgreSQL dba o relacje.

Jak surowy bazodanowy rodzic.

Stwórz Tabele

Stwórz tabelę categories:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Stwórz tabelę products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Teraz mamy dwie powiązane tabele.

categories.id jest primary key.

products.category_id jest foreign key.

To jest relacja:

categories.id -> products.category_id

Jedna kategoria może mieć wiele produktów.

Bardzo normalne.

Bardzo użyteczne.

Bardzo bazodanowe.

Wstaw Dane

Wstaw kategorie:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office');

Wstaw produkty:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Keyboard', 70.00, 1),
  ('Desk Chair', 150.00, 2),
  ('Bookshelf', 120.00, 2),
  ('Notebook', 5.00, 3),
  ('Pen', 2.00, 3);

Sprawdź obie tabele:

SELECT * FROM categories;
SELECT * FROM products;

Tabela products pokazuje category_id.

To jest poprawne.

Ale niezbyt piękne.

Baza danych zna połączenie.

Teraz musimy poprosić ją, żeby je pokazała.

To jest zadanie JOIN.

Problem Bez JOIN

Uruchom:

SELECT * FROM products;

Zobaczysz coś podobnego:

id | name       | price  | category_id
---|------------|--------|------------
1  | Laptop     | 900.00 | 1
2  | Mouse      | 25.00  | 1
3  | Keyboard   | 70.00  | 1
4  | Desk Chair | 150.00 | 2

To jest przydatne dla PostgreSQL.

Ale dla człowieka category_id = 1 nie jest zbyt pomocne.

Musimy zobaczyć nazwę kategorii.

Nazwa kategorii jest w innej tabeli.

Więc musimy połączyć:

products
categories

Dokładnie to robi JOIN.

Co To Jest JOIN?

JOIN łączy wiersze z dwóch lub więcej tabel.

Używa relacji między tabelami.

Przykładowa relacja:

products.category_id = categories.id

To oznacza:

Produkt należy do kategorii, gdy jego category_id pasuje do id kategorii.

Podstawowe query z JOIN wygląda tak:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

To mówi PostgreSQL:

Zacznij od products.
Połącz categories.
Dopasuj wiersze, gdzie products.category_id jest równe categories.id.
Pokaż nazwę produktu i nazwę kategorii.

To jest serce JOIN.

Tabele się spotykają.

Wiersze się dopasowują.

Dane stają się czytelne.

Bardzo cywilizowane.

Twój Pierwszy JOIN

Uruchom to query:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

Powinieneś zobaczyć coś takiego:

name       | name
-----------|-------------
Laptop     | Electronics
Mouse      | Electronics
Keyboard   | Electronics
Desk Chair | Furniture
Bookshelf  | Furniture
Notebook   | Office
Pen        | Office

To działa.

Ale jest mały problem.

Obie kolumny nazywają się name.

To może być mylące.

Jedno name to nazwa produktu.

Drugie name to nazwa kategorii.

PostgreSQL na to pozwala.

Twój mózg może nie.

Dlatego powinniśmy użyć aliasów.

Aliasy Kolumn

Alias kolumny daje lepszą nazwę kolumnie w wyniku.

Używamy AS.

Przykład:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Teraz wynik jest czytelniejszy:

product_name | category_name
-------------|---------------
Laptop       | Electronics
Mouse        | Electronics
Keyboard     | Electronics
Desk Chair   | Furniture
Bookshelf    | Furniture
Notebook     | Office
Pen          | Office

Dużo lepiej.

Aliasy sprawiają, że wyniki query są łatwiejsze do czytania.

Baza danych robi tę samą pracę.

Ale ludzie są mniej zdezorientowani.

To zawsze dobrze.

Zwykle.

Wybieranie Więcej Kolumn

Możesz wybierać więcej kolumn z obu tabel.

Przykład:

SELECT
  products.id,
  products.name AS product_name,
  products.price,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

To daje:

id | product_name | price  | category_name
---|--------------|--------|---------------
1  | Laptop       | 900.00 | Electronics
2  | Mouse        | 25.00  | Electronics
3  | Keyboard     | 70.00  | Electronics

Teraz mamy użyteczne dane produktu i czytelne dane kategorii.

To bardzo częste w prawdziwych aplikacjach.

Użytkownik nie chce:

category_id = 1

Użytkownik chce:

Electronics

Twoja aplikacja może pokazać ładniejsze dane, bo SQL zrobił join.

Dobry SQL sprawia, że frontend jest szczęśliwszy.

A szczęśliwy frontend mniej narzeka.

Czasami.

Aliasy Tabel

Pisanie pełnych nazw tabel może być długie.

To:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

działa.

Ale możemy skrócić to przez aliasy tabel:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Tutaj:

• p oznacza products;
• c oznacza categories.

To jest krótsze.

Bardzo częste.

Profesjonalny SQL często używa aliasów.

Nie dlatego, że developerzy kochają tajemnicze literki.

No dobrze, może trochę.

Ale głównie dlatego, że query stają się łatwiejsze do pisania.

JOIN z WHERE

Możesz używać WHERE razem z JOIN.

Przykład:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics';

To zwraca tylko produkty z kategorii Electronics.

Przykładowy wynik:

Laptop   | 900.00 | Electronics
Mouse    | 25.00  | Electronics
Keyboard | 70.00  | Electronics

To jest potężne.

Filtrujemy po danych z połączonej tabeli.

Tabela products nie przechowuje Electronics.

Przechowuje category_id.

Ale z JOIN możemy filtrować po nazwie kategorii.

Bardzo użyteczne.

Bardzo realne.

JOIN z ORDER BY

Możesz też sortować połączone wyniki.

Przykład:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.price DESC;

To sortuje produkty od najdroższego do najtańszego.

Możesz też sortować po nazwie kategorii:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY c.name ASC, p.name ASC;

To sortuje najpierw po kategorii.

Potem po nazwie produktu.

Bardzo schludnie.

Bardzo zorganizowanie.

Jak baza danych, która posprzątała swój pokój.

Rzadkie, ale piękne.

INNER JOIN

Kiedy piszesz:

JOIN categories

PostgreSQL traktuje to jak:

INNER JOIN categories

Więc te dwa query są równoważne:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

i:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.id;

INNER JOIN zwraca tylko wiersze, które mają dopasowanie w obu tabelach.

Jeśli produkt nie ma pasującej kategorii, nie pojawi się.

Ale ponieważ używamy foreign key, PostgreSQL normalnie blokuje produkty z nieprawidłowymi kategoriami.

Dlatego INNER JOIN często jest dokładnie tym, czego potrzebujemy.

Czysto.

Surowo.

Użytecznie.

LEFT JOIN

LEFT JOIN zwraca wszystkie wiersze z lewej tabeli.

Nawet jeśli nie ma dopasowania w prawej tabeli.

Przykład:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

To oznacza:

Pokaż wszystkie produkty.
Jeśli kategoria istnieje, pokaż ją.
Jeśli nie, pokaż NULL.

W naszej obecnej tabeli każdy produkt ma poprawną kategorię.

Więc wynik wygląda tak samo jak przy INNER JOIN.

Ale LEFT JOIN jest bardzo przydatny, kiedy niektóre wiersze mogą nie mieć powiązanych danych.

Na przykład:

users i profiles
customers i orders
posts i comments

Czasami chcesz pokazać wszystkich users, nawet tych bez profiles.

Właśnie tam przydaje się LEFT JOIN.

INNER JOIN vs LEFT JOIN

Prosta zasada:

Używaj INNER JOIN, kiedy chcesz tylko wiersze z dopasowaniem.

Używaj LEFT JOIN, kiedy chcesz wszystkie wiersze z pierwszej tabeli, nawet jeśli druga tabela nie ma dopasowania.

Przykład:

INNER JOIN:
Tylko produkty z kategoriami.

LEFT JOIN:
Wszystkie produkty, z kategorią, jeśli istnieje.

Pomyśl o INNER JOIN jak o surowej imprezie:

Nie ma dopasowania, nie ma wejścia.

Pomyśl o LEFT JOIN jak o milszej imprezie:

Wejdź mimo wszystko. Pokażemy NULL, jeśli twój znajomy zaginął.

Imprezy bazodanowe są dziwne.

Ale edukacyjne.

Przykład dla LEFT JOIN

Pozwólmy na produkt bez kategorii.

Najpierw usuń starą tabelę products:

DROP TABLE IF EXISTS products;

Stwórz ją ponownie:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Ta tabela nadal pozwala, żeby category_id było NULL, ponieważ nie napisaliśmy NOT NULL.

Wstaw produkty:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Mystery Box', 50.00, NULL);

Teraz uruchom INNER JOIN:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.id;

Mystery Box się nie pojawi.

Dlaczego?

Bo nie ma pasującej kategorii.

Teraz uruchom LEFT JOIN:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Teraz Mystery Box się pojawi.

Jego kategoria to NULL.

To jest różnica.

Małe słowo.

Duże zachowanie.

JOIN Więcej Niż Dwóch Tabel

Możesz łączyć więcej niż dwie tabele.

Wyobraź sobie:

customers
orders
products

Ale zróbmy prostszy przykład:

authors
posts
comments

Na razie użyjemy:

customers
orders

Usuń stare tabele, jeśli trzeba:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

Stwórz customers:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Stwórz orders:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10, 2) CHECK (total >= 0),
  customer_id INTEGER REFERENCES customers(id)
);

Wstaw customers:

INSERT INTO customers (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia');

Wstaw orders:

INSERT INTO orders (total, customer_id)
VALUES
  (49.99, 1),
  (120.00, 1),
  (35.50, 2);

Teraz zrób join:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id;

Wynik:

order_id | customer_name | total
---------|---------------|-------
1        | Anna          | 49.99
2        | Anna          | 120.00
3        | Marco         | 35.50

Zauważ, że Sofia nie ma orders.

Przy INNER JOIN Sofia się nie pojawia.

Przy LEFT JOIN możemy ją też pokazać.

SELECT
  c.name AS customer_name,
  o.total
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id;

Teraz Sofia pojawia się z NULL total.

Bardzo przydatne dla raportów.

Na przykład:

Pokaż wszystkich klientów, nawet klientów bez zamówień.

To klasyczna sytuacja dla LEFT JOIN.

Typowe Błędy

Zapominanie Warunku ON

Źle:

SELECT *
FROM products
JOIN categories;

To nie jest to, czego chcesz.

Musisz powiedzieć PostgreSQL, jak tabele są połączone.

Poprawnie:

SELECT *
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Warunek ON jest mostem.

Bez niego nie łączysz tabel prawidłowo.

Zapraszasz chaos na kolację.

Łączenie Złych Kolumn

Źle:

ON p.id = c.id

To może przypadkowo dopasować wiersze z takim samym ID.

Ale to nie jest relacja.

Poprawnie:

ON p.category_id = c.id

Foreign key wskazuje na primary key.

Używaj prawdziwej relacji.

Nie łącz kolumn tylko dlatego, że liczby wyglądają przyjaźnie.

Liczby kłamią.

Szczególnie w bazach danych.

Nie Używać Aliasów

To działa:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

Ale to jest czytelniejsze:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Aliasy sprawiają, że query są łatwiejsze do czytania.

A czytelny SQL ma mniejszą szansę zdradzić cię o północy.

Wybieranie Niejednoznacznych Kolumn

To może być mylące:

SELECT name
FROM products
JOIN categories
ON products.category_id = categories.id;

Obie tabele mają kolumnę name.

PostgreSQL może narzekać albo query może być niejasne.

Lepiej:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Zawsze bądź jasny, kiedy kolumny mają tę samą nazwę.

Baza danych lubi jasność.

Ludzie też.

Przeważnie.

Praktyka

Napisz te query.

Pokaż nazwy produktów z nazwami kategorii:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Pokaż nazwy produktów, ceny i nazwy kategorii:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Pokaż tylko produkty Electronics:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics';

Pokaż produkty posortowane po cenie:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.price DESC;

Pokaż wszystkie produkty, nawet te bez kategorii:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Uruchom query.

Zmieniaj je.

Psuj je.

Naprawiaj je.

To nadal najlepszy sposób nauki.

SQL nie wchodzi do głowy samą teorią.

Wchodzi przez błędy.

Bardzo edukacyjne błędy.

Mini Wyzwanie

Stwórz dwie tabele:

authors
posts

Reguły:

• jeden author może mieć wiele posts;
• każdy post należy do jednego author;
• imię author jest wymagane;
• title post jest wymagany;
• content post może być długim tekstem.

Stwórz tabele:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  author_id INTEGER REFERENCES authors(id)
);

Wstaw dane:

INSERT INTO authors (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia');

INSERT INTO posts (title, content, author_id)
VALUES
  ('First Post', 'Hello from PostgreSQL.', 1),
  ('SQL Notes', 'JOIN makes tables readable.', 1),
  ('Database Life', 'Data wants structure.', 2);

Teraz napisz query, które:

• pokazują wszystkie posts z nazwami authors;
• pokazują tylko posts autorstwa Anna;
• pokazują authors i ich posts przez LEFT JOIN;
• pokazują authors nawet wtedy, gdy nie mają posts;
• sortują posts po title.

To wyzwanie jest bardzo blisko prawdziwych systemów blogowych.

Authors.

Posts.

Relacje.

Query.

Bardzo realne.

Bez magii.

Tylko keys i joins.

Piękna mała maszyneria.

Podsumowanie

Dzisiaj nauczyłeś się:

• JOIN łączy dane z powiązanych tabel;
• JOIN używa relacji między tabelami;
• INNER JOIN zwraca tylko pasujące wiersze;
• LEFT JOIN zwraca wszystkie wiersze z lewej tabeli;
• foreign keys pomagają poprawnie łączyć tabele;
• ON definiuje, jak tabele są połączone;
• aliasy ułatwiają czytanie query;
• aliasy kolumn sprawiają, że wyniki są czytelniejsze;
• WHERE może filtrować połączone wyniki;
• ORDER BY może sortować połączone wyniki;
• łączenie złych kolumn daje złe dane;
• JOIN sprawia, że relacje stają się widoczne.

To ogromny krok.

Twoje tabele nie są już izolowane.

Możesz łączyć dane.

Możesz pokazywać czytelne wyniki.

Możesz przestać pokazywać użytkownikom tajemnicze ID i zacząć pokazywać prawdziwe nazwy.

To jest bazodanowy postęp.

Małe query.

Duży upgrade.

Następna Lekcja

W następnej lekcji nauczymy się aggregate functions.

Będziemy liczyć wiersze.

Obliczać sumy.

Znajdować średnie.

Znajdować wartości minimalne i maksymalne.

Innymi słowy, zaczniemy zadawać pytania typu:

Ile mamy produktów?
Jaka jest całkowita wartość wszystkich orders?
Jaka jest średnia cena?
Który produkt jest najdroższy?

PostgreSQL odpowie.

Grzecznie.

Jeśli dobrze napiszemy query.