SQL JOIN 視覺化
透過兩張合成資料表,比較七種 JOIN 範例的配對、重複及未配對資料列。
sql-join-visualizer本機處理
正在啟用本機工具…
| id | code | name |
|---|---|---|
| 1 | 10 | Mira |
| 2 | 20 | Noah |
| 3 | 20 | Ren |
| 4 | 30 | Inez |
| 5 | NULL | Sol |
| id | member_code | activity |
|---|---|---|
| 101 | 10 | Design |
| 102 | 20 | SQL |
| 103 | 20 | Data |
| 104 | 40 | Systems |
| 105 | NULL | Testing |
依 members.code = visits.member_code 連接。每組非 NULL 的等值配對各產生一列。
5 列 · 5 列已配對 · 0 列僅在左側 · 0 列僅在右側
窄螢幕可左右捲動結果表格。
| member_id | member_code | member_name | visit_id | visit_code | visit_activity |
|---|---|---|---|---|---|
| 1 | 10 | Mira | 101 | 10 | Design |
| 2 | 20 | Noah | 102 | 20 | SQL |
| 2 | 20 | Noah | 103 | 20 | Data |
| 3 | 20 | Ren | 102 | 20 | SQL |
| 3 | 20 | Ren | 103 | 20 | Data |
預覽最多 32,000 字元;複製、下載與展開文字均保留完整內容。複製失敗時可選取完整文字。
SELECT m.id AS member_id, m.code AS member_code, m.name AS member_name,
v.id AS visit_id, v.member_code AS visit_code, v.activity AS visit_activity
FROM members AS m
INNER JOIN visits AS v ON m.code = v.member_code
ORDER BY CASE WHEN m.id IS NULL THEN 1 ELSE 0 END, m.id, v.id;展開可選取的完整文字
SELECT m.id AS member_id, m.code AS member_code, m.name AS member_name,
v.id AS visit_id, v.member_code AS visit_code, v.activity AS visit_activity
FROM members AS m
INNER JOIN visits AS v ON m.code = v.member_code
ORDER BY CASE WHEN m.id IS NULL THEN 1 ELSE 0 END, m.id, v.id;如何使用這個工具
選擇連接類型來比較來源表與結果。鍵值 20 在兩側各有兩列,因此形成四組配對。等值連接時,兩側即使都有 NULL 鍵,仍會各自保留為未配對列。
不適合用在什麼情況
三種只保留未配對列的範例,透過對不可為 NULL 的列 ID 加上 IS NULL,篩選外部連接結果。SQLite 自 3.39.0 起支援 RIGHT JOIN 與 FULL OUTER JOIN。MySQL 支援 RIGHT JOIN,兩種 FULL OUTER JOIN 範例則需要改寫。頁面在瀏覽器內呈現模擬結果。