JOINy s\u0142u\u017c\u0105 do \u0142\u0105czenia danych z wielu tabel. Poni\u017cej znajdziesz najwa\u017cniejsze typy JOIN wraz z przyk\u0142adami i wizualizacj\u0105.<\/p>\n\n
Za\u0142\u00f3\u017cmy dwie tabele:<\/p>\n\n
users<\/code><\/li>\n\n\n\norders<\/code><\/li>\n<\/ul>\n\n
\n\n\ud83d\udd39 1. INNER JOIN<\/h1>\n\n
Zwraca tylko wsp\u00f3lne dane.<\/p>\n\n
users orders
A A
B B
C
Result:
A
B<\/code><\/pre>\n\nSELECT *
FROM users
INNER JOIN orders ON users.id = orders.user_id;<\/code><\/pre>\n\n
\n\n\ud83d\udd39 2. LEFT JOIN<\/h1>\n\n
Wszystko z lewej + dopasowania z prawej.<\/p>\n\n
users orders
A A
B B
C
Result:
A
B
C (NULL)<\/code><\/pre>\n\nSELECT *
FROM users
LEFT JOIN orders ON users.id = orders.user_id;<\/code><\/pre>\n\n
\n\n\ud83d\udd39 3. RIGHT JOIN<\/h1>\n\n
Wszystko z prawej + dopasowania z lewej.<\/p>\n\n
users orders
A A
B B
D
Result:
A
B
D (NULL)<\/code><\/pre>\n\n
\n\n\ud83d\udd39 4. FULL OUTER JOIN<\/h1>\n\n
Wszystko z obu tabel.<\/p>\n\n
users orders
A A
B B
C D
Result:
A
B
C (NULL)
D (NULL)<\/code><\/pre>\n\nSELECT *
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;<\/code><\/pre>\n\n\ud83d\udc49 MySQL: trzeba robi\u0107 UNION<\/p>\n\n
\n\n\ud83d\udd39 5. CROSS JOIN<\/h1>\n\n
Ka\u017cdy z ka\u017cdym (iloczyn kartezja\u0144ski).<\/p>\n\n
users: A, B
orders: X, Y
Result:
A-X
A-Y
B-X
B-Y<\/code><\/pre>\n\nSELECT *
FROM users
CROSS JOIN orders;<\/code><\/pre>\n\n
\n\n\ud83d\udd39 6. SELF JOIN<\/h1>\n\n
Tabela \u0142\u0105czy si\u0119 sama ze sob\u0105.<\/p>\n\n
SELECT a.name, b.name
FROM users a
JOIN users b ON a.manager_id = b.id;<\/code><\/pre>\n\n\ud83d\udc49 u\u017cywane np. w hierarchiach<\/p>\n\n
\n\n\ud83d\udd39 7. LEFT JOIN + WHERE NULL (ANTI JOIN)<\/h1>\n\n
Zwraca rekordy bez dopasowania.<\/p>\n\n
users orders
A A
B
C
Result:
B
C<\/code><\/pre>\n\nSELECT users.*
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;<\/code><\/pre>\n\n
\n\n\ud83d\udd39 8. INNER JOIN z dodatkowymi warunkami<\/h1>\n\n
JOIN + filtr logiczny.<\/p>\n\n
SELECT *
FROM users
JOIN orders
ON users.id = orders.user_id
AND orders.status = 'paid';<\/code><\/pre>\n\n
\n\n\ud83d\udd39 9. UNION jako alternatywa FULL JOIN<\/h1>\n\n
(MySQL workaround)<\/p>\n\n
SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT * FROM users
RIGHT JOIN orders ON users.id = orders.user_id;<\/code><\/pre>\n\n
\n\n\ud83d\udd25 Podsumowanie (wa\u017cne w praktyce)<\/h1>\n\n\n- INNER JOIN \u2192 najcz\u0119\u015bciej u\u017cywany<\/li>\n\n\n\n
- LEFT JOIN \u2192 backend standard<\/li>\n\n\n\n
- RIGHT JOIN \u2192 rzadko potrzebny<\/li>\n\n\n\n
- FULL JOIN \u2192 raporty \/ analizy<\/li>\n\n\n\n
- CROSS JOIN \u2192 prawie nigdy w produkcji<\/li>\n\n\n\n
- SELF JOIN \u2192 relacje hierarchiczne<\/li>\n\n\n\n
- ANTI JOIN \u2192 bardzo przydatny (np. brakuj\u0105ce dane)<\/li>\n<\/ul>\n\n
\n\n\ud83d\udca1 Bonus (Laravel)<\/h1>\n\n
W Laravelu INNER JOIN to:<\/p>\n\n
User::join('orders', 'users.id', '=', 'orders.user_id')->get();<\/code><\/pre>\n\nale cz\u0119sto lepiej u\u017cywa\u0107:<\/p>\n\n
eager loading (with()<\/code>)<\/p>\n\nrelacji Eloquent
LEFT JOIN:
User::leftJoin(‘orders’, ‘users.id’, ‘=’, ‘orders.user_id’)->get();
RIGHT JOIN
User::rightJoin(‘orders’, ‘users.id’, ‘=’, ‘orders.user_id’)->get();
\u26a0\ufe0f Wa\u017cna rzecz (Laravel mindset)<\/p>\n\n
To nie jest \u201eEloquent w pe\u0142ni\u201d, tylko:<\/p>\n\n
\ud83d\udc49 Query Builder przez Eloquent<\/strong><\/p>\n\nBo:<\/p>\n\n
\n- tracisz relacje (
hasMany<\/code>, belongsTo<\/code>)<\/li>\n\n\n\n- tracisz modele w czystej formie<\/li>\n\n\n\n
- dostajesz bardziej \u201eSQL-like\u201d wynik<\/li>\n<\/ul>\n\n
\n\n\ud83e\udde0 Lepsze podej\u015bcie w Laravel (cz\u0119sto)<\/h1>\n\n
Zamiast JOIN:<\/p>\n\n
User::with(‘orders’)->get();<\/p>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-1","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"acf":[],"_links":{"self":[{"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/posts\/1","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/comments?post=1"}],"version-history":[{"count":4,"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/posts\/1\/revisions"}],"predecessor-version":[{"id":331,"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/posts\/1\/revisions\/331"}],"wp:attachment":[{"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/media?parent=1"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/categories?post=1"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/piotrswitlicki.pl\/pl\/wp-json\/wp\/v2\/tags?post=1"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}