嗨，大家好！

昨天在腦死前看了許多圖解JOIN 的概念，但我們能實際應用嗎？

今天給自己的任務是利用之前建的sample Db 來嘗試圖2 的前7 種JOIN 情境

[圖2]搭配JOIN types 和其稱呼：

低標：no syntax error
均標：能完成7 種JOIN
前標：能用現有的Db 想出情境並搭配這7 種JOIN

讓我們開始吧！

不過在練習前，我們先刪除一些users，result set 才不會看起來都一樣 XD

irb(main):001:0> User.delete(User.all.sample(3))
  User Load (0.8ms)  SELECT "users".* FROM "users"
  User Destroy (2.9ms)  DELETE FROM "users" WHERE "users"."id" IN ($1, $2, $3)  [["id", 2], ["id", 1], ["id", 6]]
3

• Inner Join:

SELECT "userId", name, todos.id, completed FROM todos
JOIN users ON "userId" = users.id

有140 rows，應該沒錯吧？

兩表都有的才有，所以是200 -（20x3) = 140 rows

• Left Join:

SELECT "userId", name, td.id, completed FROM todos td
LEFT JOIN users ur ON td."userId" = ur.id

左表有的都有，所以是200 rows，但有很多NULL

• Right Join:

SELECT "userId", name, td.id, completed FROM todos td
RIGHT JOIN users ur ON td."userId" = ur.id

右表有的才有，所以是200 -（20x3) = 140 rows

• Full Outer Join:

SELECT "userId", name, td.id, completed FROM todos td
FULL OUTER JOIN users ur ON td."userId" = ur.id

兩表通通拉進來，所以是200 rows

• Outer Minus Join:

SELECT "userId", name, td.id, completed FROM todos td
FULL OUTER JOIN users ur ON td."userId" = ur.id
WHERE td."userId" IS NULL 
OR ur.id IS NULL 

共同有的都沒有，所以是200 -（20x7) = 60 rows

• Right Minus Join:

SELECT "userId", name, td.id, completed FROM todos td
RIGHT JOIN users ur ON td."userId" = ur.id
WHERE td."userId" IS NULL

只有左表是NULL 的才有，所以是0 rows，因為全都有值！

（有點難理解 XD）

• Left Minus Join:

SELECT "userId", name, td.id, completed FROM todos td
LEFT JOIN users ur ON td."userId" = ur.id
WHERE ur.name IS NULL

只有右表是NULL 的才有，所以是20x3 = 60 rows

低標、均標完成！！！（自己說 = =）

那讓被翻爛的書試著往前標邁進吧......