Dokumentacja SQLite z przykładami: INNER JOIN

Q: Co robi INNER JOIN w SQLite?

INNER JOIN zwraca wiersze, które mają dopasowanie w obu tabelach zgodnie z warunkiem ON. Wiersze z którejkolwiek strony bez dopasowania są pomijane. To złączenie domyślne: w SQLite JOIN i INNER JOIN znaczą to samo.

Q: Jaka jest różnica między INNER JOIN a LEFT JOIN w SQLite?

INNER JOIN zostawia tylko dopasowane wiersze. LEFT JOIN zostawia każdy wiersz z lewej tabeli i wstawia NULL po prawej stronie, gdy brak dopasowania. Używaj INNER JOIN, gdy brak dopasowania oznacza „pomiń ten wiersz”, a LEFT JOIN, gdy oznacza „pokaż go mimo to”.

Q: Czy można złączyć trzy tabele przez INNER JOIN w SQLite?

Tak: dopisz kolejną klauzulę JOIN ... ON .... Każde złączenie łączy bieżący wynik z nową tabelą. Nie ma sztywnego limitu, ale powyżej czterech lub pięciu tabel czytelność szybko spada i wtedy często pomaga CTE.

Q: Kiedy używać USING zamiast ON?

USING (column) to skrót na sytuację, gdy kolumna złączenia ma tę samą nazwę w obu tabelach. Jest krótszy i scala zduplikowaną kolumnę w jedną w wyniku. Używaj ON, gdy nazwy kolumn się różnią albo potrzebujesz bardziej złożonego warunku.

Złączenie zszywa dwie tabele

Relacyjne bazy danych celowo rozkładają dane na tabele: klienci w jednej, zamówienia w drugiej, produkty w trzeciej. Dzięki temu każdy fakt jest w jednym miejscu. Ale gdy chcesz odpowiedzieć na prawdziwe pytanie („który klient co zamówił?”), musisz złożyć te kawałki z powrotem. Do tego służy złączenie.

INNER JOIN to koń pociągowy. Łączy w pary wiersze z dwóch tabel wszędzie tam, gdzie warunek jest spełniony, a całą resztę odrzuca.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 80.00);

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;

Trzech klientów, trzy zamówienia, ale Chen nie ma żadnego zamówienia, więc się nie pojawia. Na tym polega „inner”: przetrwają tylko dopasowane wiersze.

Model myślowy: dopasuj wiersze, potem filtruj

Czytaj INNER JOIN tak: weź każdy wiersz z pierwszej tabeli, spójrz na każdy wiersz z drugiej i zachowaj parę tylko wtedy, gdy warunek ON jest prawdziwy. Koncepcyjnie to ogromny iloczyn kartezjański, a po nim filtr. SQLite w rzeczywistości tak tego nie robi (gdy może, korzysta z indeksów), ale ten model poprawnie przewiduje wynik.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 1, 70), (12, 2, 15);

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

Kilka nawyków, które warto tu przyswoić:

Nadawaj tabelom aliasy (customers AS c), gdy wspominasz je więcej niż raz. Mniej szumu.
Kwalifikuj kolumny (c.name, o.total), gdy mogłyby należeć do obu tabel.
Kolejność w ON o.customer_id = c.id nie ma znaczenia: c.id = o.customer_id działa tak samo.

INNER JOIN a JOIN

W SQLite (i w standardowym SQL) samo JOIN oznacza INNER JOIN. Słowo kluczowe INNER jest opcjonalne.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

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

Oba zapisy dają ten sam plan i te same wiersze. Pisanie pełnego INNER JOIN to drobny zysk dla czytelności w kodzie, który miesza typy złączeń: intencja jest oczywista obok LEFT JOIN kilka linii niżej.

ON a USING

Gdy kolumny złączenia mają tę samą nazwę w obu tabelach, USING (column) jest krótsze niż ON a.col = b.col:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT name, total
FROM customers
INNER JOIN orders USING (customer_id);

USING (customer_id) robi dwie rzeczy: dopasowuje równe customer_id i scala kolumnę, żeby pojawiła się w wyniku raz. Sięgaj po nie, gdy obie strony naprawdę używają tej samej nazwy. Zostań przy ON, gdy nazwy się różnią (orders.customer_id = customers.id) albo warunek to coś więcej niż pojedyncza równość.

Złączenie trzech tabel

Łącz złączenia w łańcuch, dodając kolejne klauzule JOIN ... ON .... Każda łączy bieżący wynik z kolejną tabelą.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TABLE order_items (
    order_id INTEGER,
    product TEXT,
    quantity INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);
INSERT INTO order_items VALUES (10, 'Zeszyt', 2), (10, 'Długopis', 5), (11, 'Kubek', 1);

SELECT c.name, o.id AS order_id, i.product, i.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;

Czytaj od góry do dołu: klienci łączą się z zamówieniami, zamówienia z pozycjami. Każdy wiersz wyniku to jedna kombinacja klienta, zamówienia i pozycji. Wszystko, czemu na którymkolwiek etapie łańcucha brakuje dopasowania, jest odrzucane: to zasada złączenia wewnętrznego stosowana na każdym kroku.

Filtrowanie przez WHERE

ON mówi, jak łączyć wiersze w pary. WHERE filtruje połączony wynik. W przypadku złączeń wewnętrznych dodatkowy warunek w ON i w WHERE daje te same wiersze, ale przyjęło się trzymać warunki złączenia w ON, a filtry wierszy w WHERE.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada', 'UK'), (2, 'Boris', 'FR'), (3, 'Chen', 'UK');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 80), (12, 3, 12);

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.total > 20;

Czyta się to jako „złącz klientów i zamówienia, a potem zostaw tylko klientów z UK, których zamówienie przekracza 20”. Dwie role, dwie klauzule: przyszły ty będzie wdzięczny. (Gdy zaczniesz pisać LEFT JOIN, różnica między ON a WHERE przestanie być kosmetyczna, ale to temat następnej strony.)

Wiele warunków w ON

ON może zawierać dowolne wyrażenie logiczne, nie tylko jedną równość. Przydaje się, gdy relacja obejmuje więcej niż jedną kolumnę albo gdy chcesz odfiltrować prawą stronę już podczas złączenia.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES
    (10, 1, 30, 'paid'),
    (11, 1, 50, 'cancelled'),
    (12, 2, 15, 'paid');

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id
   AND o.status = 'paid';

Anulowane zamówienie znika, bo drugi warunek nie jest spełniony. Przy złączeniu wewnętrznym równie dobrze możesz napisać WHERE o.status = 'paid' i dostać ten sam wynik. Wersja z ON trzyma logikę „co liczy się jako dopasowanie” blisko złączenia.

Częste pułapki

Kilka rzeczy, na których ludzie się potykają:

Brak klauzuli ON. FROM a INNER JOIN b bez ON to w SQLite błąd składni. (Sam przecinek, FROM a, b, się kompiluje, daje iloczyn kartezjański i prawie nigdy nie jest tym, czego chciałeś.)
Nieoczekiwane duplikaty. Jeśli klient ma trzy zamówienia, jego imię pojawi się w wyniku trzy razy. To poprawne zachowanie złączenia, a nie błąd. Jeśli chcesz jeden wiersz na klienta, agreguj przez GROUP BY.
Brakujące wiersze. Jeśli klient powinien się pojawić, a się nie pojawił, warunek złączenia nie został spełniony. Sprawdź, czy w kolumnach złączenia nie ma NULL, albo sięgnij po LEFT JOIN.
Niejednoznaczne nazwy kolumn. SELECT id FROM customers JOIN orders ON ... kończy się błędem, bo obie tabele mają id. Zakwalifikuj kolumnę: c.id albo o.id.

Dalej: LEFT JOIN

INNER JOIN świetnie się sprawdza, gdy brak dopasowania oznacza „pomiń ten wiersz”. Czasem jednak chcesz wypisać każdego klienta, nawet bez zamówień, z NULL w miejscu brakujących danych. To LEFT JOIN, omówiony w następnej części.

Najczęściej zadawane pytania

Co robi INNER JOIN w SQLite?

INNER JOIN zwraca wiersze, które mają dopasowanie w obu tabelach zgodnie z warunkiem ON. Wiersze z którejkolwiek strony bez dopasowania są pomijane. To złączenie domyślne: w SQLite JOIN i INNER JOIN znaczą to samo.

Jaka jest różnica między INNER JOIN a LEFT JOIN w SQLite?

INNER JOIN zostawia tylko dopasowane wiersze. LEFT JOIN zostawia każdy wiersz z lewej tabeli i wstawia NULL po prawej stronie, gdy brak dopasowania. Używaj INNER JOIN, gdy brak dopasowania oznacza „pomiń ten wiersz”, a LEFT JOIN, gdy oznacza „pokaż go mimo to”.

Czy można złączyć trzy tabele przez INNER JOIN w SQLite?

Tak: dopisz kolejną klauzulę JOIN ... ON .... Każde złączenie łączy bieżący wynik z nową tabelą. Nie ma sztywnego limitu, ale powyżej czterech lub pięciu tabel czytelność szybko spada i wtedy często pomaga CTE.

Kiedy używać USING zamiast ON?

USING (column) to skrót na sytuację, gdy kolumna złączenia ma tę samą nazwę w obu tabelach. Jest krótszy i scala zduplikowaną kolumnę w jedną w wyniku. Używaj ON, gdy nazwy kolumn się różnią albo potrzebujesz bardziej złożonego warunku.

Powiązane zagadnienia