MySQL Join comparison and analysis

Learn about the join operation, including the left Join,right Join,inner join and out join, the size of the table generated after each operation is different; Suppose A and B tables do join operations, and if it is a left join, the A table is the benchmark. Then match with the B table, the size of the table is at least the size of a table (multiplied by the maximum coverage of the connections in table B and a table, and the maximum number of bars recorded in table B for a record in Table a); The right join is based on table B; Inner join, A and B the intersection after intersection; outer intersection, then the result of a and B union.

(The following illustrations and links are very clear, please refer to http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins)

Introduction

This was just a simple article visually explaining SQL JOIN S.

Background

I ' m a pretty visual person. Things seem to make + sense as a picture. I looked the Internet for a good graphical representation JOIN of SQL s, but I couldn ' t find any to my liking. Some had good diagrams but lacked completeness (they didn ' t has all the possible JOINs), and Some were just plain terribl E. So, I decided to create my own and write a article about it.

Using the Code

I am going to discuss seven different ways the can return data from both relational tables. I'll be the excluding cross Joins and the self referencing Joins. The seven Joins I'll discuss is 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 is not really joining the "the" and "tables", but for simplicity, I'll still refer to these as Joins because you use a SQL Join in each of the these queries (but exclude some records with a WHERE clause).

Inner JOIN

The simplest, most understood Join and are the most common. This query would return all of the records in the left table (table A), which has A matching record in the right table (tabl e B). This Join is written as follows:

Collapse | Copy Code

<select_list> From table_a Ajoins Table_b b onA.key = B.key    

Left JOIN

This query would return all of the records in the left table (table A) regardless if any of those records has A match in T He right table (table B). It'll also return any matching records from the right table. This Join is written as follows:

Collapse | Copy Code

<select_list> From table_a Ajoins Table_b b onA.key = B.key    

Right JOIN

This query would return all of the records in the Right table (table B) regardless if any of those records has a match in The Left table (table A). It'll also return any matching records from the left table. This Join is written as follows:

Collapse | Copy Code

<select_list> From table_a Ajoins Table_b b onA.key = B.key    

Outer JOIN

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

Collapse | Copy Code

<select_list> From table_a Ajoins Table_b b onA.key = B.key    

Left excluding JOIN

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

Collapse | Copy Code

<select_list> From table_a Ajoins Table_b b onA.key = B.keyWHERE B.NULL      

Right excluding JOIN

This query would return all of the records in the Right table (table B) that does not match any records in the left table (TA Ble A). This Join is written as follows:

Collapse | Copy Code

<select_list> From table_a ajoins Table_b B onA.key = B.keyWHERE A.NULL      

Outer excluding JOIN

This query would return all of the records in the left table (table A) and all of the records in the Right table (table B) That does not match. I have yet to has 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_list> From table_a ajoins Table_b B onA.key = B.keyWHERE A.OR B.NULL
           

Examples

Suppose we have tables, table_a and table_b. The data in these tables is 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  SCOTCH

The results of the seven Joins is shown below:

Collapse | Copy Code

--INNER JOIN SELECT 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 JOIN SELECT 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_p K 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  LUCENT     null       null (8 row (s) affected)

Collapse | Copy Code

--Right JOIN SELECT 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 one       (8 row (s) affected)

Collapse | Copy Code

--OUTER JOIN SELECT 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   5 ARIZONA    null       null   4 LINCOLN    null       Null  LUCENT     null       null (row (s) affected)

Collapse | Copy Code

--left excluding JOIN SELECT 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.pkwhe RE b.pk is nulla_pk a_value    b_value    b_pk----------------------------   4 LINCOLN    null       null   5 ARIZONA    null       null  LUCENT     null       null (3 row (s) affected)

Collapse | Copy Code

--Right excluding JOIN SELECT 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.pkwh ERE a.pk is nulla_pk a_value    b_value    b_pk----------------------------NULL null       MICROSOFT     8NULL Null       APPLE         9NULL null       SCOTCH one       (3 row (s) affected)

Collapse | Copy Code

--OUTER excluding JOIN SELECT 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       Microsof T     8NULL null       APPLE         9NULL null       SCOTCH   5 ARIZONA    null       null   4 LINCOLN    Null       null  LUCENT     null       null (6 row (s) affected)

Note on OUTER JOIN The inner joined Records is returned first, followed by the right joined records, and then Finall Y the left joined records (at least, that's how my Microsoft sql Server does it; this, the course, was without using any statement).

You can visit the Wikipedia article for more info here (however, the entry are not graphical).

I ' ve also created a cheat sheet that's can print out if needed. If you right click on the image below and select "Save Target as ...", you'll download the full size image.