mysql join 对比和分析
时间:2022-03-13 22:40
学习的时候了解了一下join操作,包含left join,right join,inner join和out join,每种操作之后生成的表的空间大小不同;假设A和B表做join操作,如果是left join,则以A表为基准,然后和B表进行匹配,表的大小至少A表的大小(乘以B表中和A表中连接的最大覆盖率,及A表的一条记录所对应B表中记录的最大条数);则right join则以B表为基准;inner join,则为A和B intersection之后的交集;outer intersection,则为A和B union之后的结果。
(下面图解以及链接非常清楚,请参考)
Introduction
This is just a simple article visually explaining SQL JOIN
s.
Background
I‘m a pretty visual person. Things seem to make more sense as a picture. I looked all over the Internet for a good graphical representation of SQL JOIN
s, but I couldn‘t find any to my liking. Some had good diagrams but lacked completeness (they didn‘t have all the possible JOINs), and some were just plain terrible. So, I decided to create my own and write an article about it.
Using the code
I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins. The seven Joins I will discuss are shown below:
INNER JOIN
LEFT JOIN
RIGHT JOIN
OUTER JOIN
LEFT JOIN EXCLUDING INNER JOIN
RIGHT JOIN EXCLUDING INNER JOIN
OUTER JOIN EXCLUDING INNER JOIN
For the sake of this article, I‘ll refer to 5, 6, and 7 as LEFT EXCLUDING JOIN
, RIGHT EXCLUDING JOIN
, and OUTER EXCLUDING JOIN
, respectively. Some may argue that 5, 6, and 7 are not really joining the two tables, but for simplicity, I will still refer to these as Joins because you use a SQL Join in each of these queries (but exclude some records with a WHERE
clause).
Inner JOIN
Collapse |
SELECT <select_list> FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key
Left JOIN
Collapse |
SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key
Right JOIN
Collapse |
SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key
Outer JOIN
Collapse |
SELECT <select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key
Left Excluding JOIN
Collapse |
SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key WHERE B.Key IS NULL
Right Excluding JOIN
Collapse |
SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key WHERE A.Key IS NULL
Outer Excluding JOIN
Collapse |
SELECT <select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL
Examples
Suppose we have two tables, Table_A and Table_B. The data in these tables are shown below:
Collapse |TABLE_A PK Value ---- ---------- 1 FOX 2 COP 3 TAXI 6 WASHINGTON 7 DELL 5 ARIZONA 4 LINCOLN 10 LUCENT TABLE_B PK Value ---- ---------- 1 TROT 2 CAR 3 CAB 6 MONUMENT 7 PC 8 MICROSOFT 9 APPLE 11 SCOTCH
The results of the seven Joins are shown below:
Collapse |-- INNER JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A INNER JOIN Table_B B ON A.PK = B.PK A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7 (5 row(s) affected)Collapse |
-- LEFT JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 4 LINCOLN NULL NULL 5 ARIZONA NULL NULL 6 WASHINGTON MONUMENT 6 7 DELL PC 7 10 LUCENT NULL NULL (8 row(s) affected)Collapse |
-- RIGHT JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A RIGHT JOIN Table_B B ON A.PK = B.PK A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7 NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 (8 row(s) affected)Collapse |
-- OUTER JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 1 FOX TROT 1 2 COP CAR 2 3 TAXI CAB 3 6 WASHINGTON MONUMENT 6 7 DELL PC 7 NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 5 ARIZONA NULL NULL 4 LINCOLN NULL NULL 10 LUCENT NULL NULL (11 row(s) affected)Collapse |
-- LEFT EXCLUDING JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK WHERE B.PK IS NULL A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- 4 LINCOLN NULL NULL 5 ARIZONA NULL NULL 10 LUCENT NULL NULL (3 row(s) affected)Collapse |
-- RIGHT EXCLUDING JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A RIGHT JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 (3 row(s) affected)Collapse |
-- OUTER EXCLUDING JOIN SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL OR B.PK IS NULL A_PK A_Value B_Value B_PK ---- ---------- ---------- ---- NULL NULL MICROSOFT 8 NULL NULL APPLE 9 NULL NULL SCOTCH 11 5 ARIZONA NULL NULL 4 LINCOLN NULL NULL 10 LUCENT NULL NULL (6 row(s) affected)
Note on the OUTER JOIN
that the inner joined records are returned first, followed by the right joined records, and then finally the left joined records (at least, that‘s how my Microsoft SQL Server did it; this, of course, is without using any ORDER BY
statement).
You can visit the Wikipedia article for more info (however, the entry is not graphical).
I‘ve also created a cheat sheet that you can print out if needed. If you right click on the image below and select "Save Target As...", you will download the full size image.
mysql join 对比和分析,布布扣,bubuko.com