mysql join 對比和分析

標籤：http   os   io   strong   for   art   ar   cti   

學習的時候瞭解了一下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之後的結果。

（下面圖解以及連結非常清楚，請參考http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins）

Introduction

This is just a simple article visually explaining SQL JOINs.

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 JOINs, 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:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. OUTER JOIN
5. LEFT JOIN EXCLUDING INNER JOIN
6. RIGHT JOIN EXCLUDING INNER JOIN
7. 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

This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

 Collapse | Copy Code

SELECT <select_list> FROM Table_A AINNER JOIN Table_B BON A.Key = B.Key

Left JOIN

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

 Collapse | Copy Code

SELECT <select_list>FROM Table_A ALEFT JOIN Table_B BON A.Key = B.Key

Right JOIN

This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:

 Collapse | Copy Code

SELECT <select_list>FROM Table_A ARIGHT JOIN Table_B BON A.Key = B.Key

Outer JOIN

This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

 Collapse | Copy Code

SELECT <select_list>FROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.Key

Left Excluding JOIN

This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

 Collapse | Copy Code

SELECT <select_list> FROM Table_A ALEFT JOIN Table_B BON A.Key = B.KeyWHERE B.Key IS NULL

Right Excluding JOIN

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

 Collapse | Copy Code

SELECT <select_list>FROM Table_A ARIGHT JOIN Table_B BON A.Key = B.KeyWHERE A.Key IS NULL

Outer Excluding JOIN

This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. This Join is written as follows:

 Collapse | Copy Code

SELECT <select_list>FROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.KeyWHERE 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 | Copy Code

TABLE_A  PK Value---- ----------   1 FOX   2 COP   3 TAXI   6 WASHINGTON   7 DELL   5 ARIZONA   4 LINCOLN  10 LUCENTTABLE_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 | Copy Code

-- INNER JOINSELECT A.PK AS A_PK, A.Value AS A_Value,       B.Value AS B_Value, B.PK AS B_PKFROM Table_A AINNER JOIN Table_B BON A.PK = B.PKA_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 | Copy Code

-- LEFT JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A ALEFT JOIN Table_B BON A.PK = B.PKA_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 | Copy Code

-- RIGHT JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A ARIGHT JOIN Table_B BON A.PK = B.PKA_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            7NULL NULL       MICROSOFT     8NULL NULL       APPLE         9NULL NULL       SCOTCH       11(8 row(s) affected)

 Collapse | Copy Code

-- OUTER JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A AFULL OUTER JOIN Table_B BON A.PK = B.PKA_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            7NULL NULL       MICROSOFT     8NULL NULL       APPLE         9NULL NULL       SCOTCH       11   5 ARIZONA    NULL       NULL   4 LINCOLN    NULL       NULL  10 LUCENT     NULL       NULL(11 row(s) affected)

 Collapse | Copy Code

-- LEFT EXCLUDING JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A ALEFT JOIN Table_B BON A.PK = B.PKWHERE B.PK IS NULLA_PK A_Value    B_Value    B_PK---- ---------- ---------- ----   4 LINCOLN    NULL       NULL   5 ARIZONA    NULL       NULL  10 LUCENT     NULL       NULL(3 row(s) affected)

 Collapse | Copy Code

-- RIGHT EXCLUDING JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A ARIGHT JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULLA_PK A_Value    B_Value    B_PK---- ---------- ---------- ----NULL NULL       MICROSOFT     8NULL NULL       APPLE         9NULL NULL       SCOTCH       11(3 row(s) affected)

 Collapse | Copy Code

-- OUTER EXCLUDING JOINSELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PKFROM Table_A AFULL OUTER JOIN Table_B BON A.PK = B.PKWHERE A.PK IS NULLOR B.PK IS NULLA_PK A_Value    B_Value    B_PK---- ---------- ---------- ----NULL NULL       MICROSOFT     8NULL NULL       APPLE         9NULL 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 here (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.