發表者討論內容

冷日
(冷日) 發表時間：2012/11/13 8:08

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15773

[分享]一些來自 MySql 官網上的討論範例，超實用！

一些來自 MySql 官網上的討論範例，超實用！

Below is an example of how to left-join multiple tables independently.

SELECT ...
FROM table 1
LEFT JOIN table 2 on (table1.id = table2.id)
LEFT JOIN table 3 on (table1.id2 = table3.id2)
LEFT JOIN table 4 on (table1.id3 = table4.id3)

This is an example of using a left to get lookup values from a table twice. The reason that an outer join was used instead of an inner join, was that in this case, there may be values that are null inside of the degree table.

SELECT d.degDegId, m1.majDescription AS major_1, m2.majDescription AS major_2
FROM degree AS d
LEFT OUTER JOIN major AS m1
ON d.degMajor1 = m1.majMajId
LEFT OUTER JOIN major AS m2
ON d.degMajor2 = m2.majMajId

You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):
with two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

with three tables t1, t2, t3:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id

The result of a full outer join betwee tables A and B includes:
- rows from the result of the inner join
- rows from A that don't have corresponding rows in B
- rows from B that don't have corresponding rows in A

Formally, the corresponding SQL statement looks like this:

SELECT *
FROM A JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A LEFT JOIN B ON A.id = B.id
WHERE B.id IS NULL
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL

Due to the fact that the first union represents a left outer join, the statement can be simplified:

SELECT *
FROM A LEFT JOIN B ON A.id = B.id
UNION ALL
SELECT *
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL

原文出處：MySQL :: MySQL 5.5 Reference Manual :: 13.2.9.2 JOIN Syntax

回覆

平面展開

前一個主題 | 下一個主題 | |

| |

討論串

[轉貼]MSSQL Join總結 (冷日
(冷日), 2010/10/23 12:21)
[轉貼][MySQL] outer join 使用 (冷日
(冷日), 2012/11/13 7:40)
[轉貼][MySQL]left, right, inner, outer join 使用方法 (冷日
(冷日), 2012/11/13 7:45)
» [分享]一些來自 MySql 官網上的討論範例，超實用！ (冷日
(冷日), 2012/11/13 8:08)
[轉貼]Oracle PL/SQL SELECT 的 OUT JOIN (+) 用法 (冷日
(冷日), 2016/9/14 9:50)
[轉貼]SQL Join 的觀念 (冷日
(冷日), 2018/12/12 4:57)
[轉貼]SQL inner join、full out join、left join 的觀念 (冷日
(冷日), 2018/12/12 5:01)

發表新主題

DB研討會 : [轉貼]MSSQL Join總結

討論串