對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2012/11/13 8:08:57
一些來自 MySql 官網上的討論範例，超實用！
Below is an example of how to left-join multiple tables independently.
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.
You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):
with two tables t1, t2:
with three tables t1, t2, t3:
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:
Due to the fact that the first union represents a left outer join, the statement can be simplified:
原文出處：MySQL :: MySQL 5.5 Reference Manual :: 13.2.9.2 JOIN Syntax
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