Specific usage instructions for mysql-joins

Join syntax:
1. InnerJOIN: (Inner Join)
2. LeftJOIN: (Left Join)
3. LeftExcludingJOIN: (Left join excludes inner join results)
4. Right JOIN: (right join)
5. RightExcludingJOIN: (right join excludes inner join results)
6. OuterJOIN: (Outer Join)
7. OuterExcludingJOIN: (Outer join excludes inner join results)

The word JOIN is familiar to anyone who has been in contact with databases. Many people are familiar with various JOINs, but many people do not have a thorough understanding of it.
Assume we have two tables, Table_A and Table_B. The data in these two tables are as follows:

TABLE_A | TABLE_B
 PK Value | PK Value
---- ---------- | ---- ----------
 1 FOX | 1 TROT
 2 COP | 2 CAR
 3 TAXI | 3 CAB
 6 WASHINGTON | 6 MONUMENT
 7 DELL | 7 PC
 5 ARIZONA | 8 MICROSOFT
 4 LINCOLN | 9 APPLE
 10 LUCENT | 11 SCOTCH

Join syntax:

join_table:
 table_reference JOIN table_factor [join_condition] //Inner join | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition //Outer join | table_reference LEFT SEMI JOIN table_reference join_condition //Left semi join | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)


table_reference:
 table_factor //table | join_table //join statement table_factor:
 tbl_name [alias] //Table name [alias]
 | table_subquery alias //Subquery [alias]
 | ( table_references ) // table_reference with space


join_condition:
 ON expression //conditional statement starting with on

1. Inner JOIN: (Inner Join)

insert image description here

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

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

-- 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)

2. Left JOIN: (Left Join)

insert image description here

This query returns all records from the left table (Table A), regardless of whether they match any records in the right table (Table B). It will also return any matching records from the correct table. This connection is written as follows:

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

-- 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)

3. Left Excluding JOIN: (Left join excludes inner join results)

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

insert image description here

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

-- 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)

4. Right JOIN: (right join)

insert image description here

This query returns all records from the right table (Table B), regardless of whether any of those records match records in the left table (Table A). It will also return any matching records from the left table. This connection is written as follows:

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

--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)

5. Right Excluding JOIN: (Right join excludes inner join results)

insert image description here

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

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

-- 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)

6. Outer JOIN: (Outer Join)

insert image description here

This join may also be called a full outer join or a full join. This query returns all records from both tables, joining the records in the left table (Table A) that match records in the right table (Table B). This connection is written as follows:

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

-- 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)

7. Outer Excluding JOIN: (Outer join excludes inner join results)

insert image description here

This query will return all records from the left table (Table A) and all records from the right table (Table B) that do not have a match. I haven't needed to use this type of join yet, but I use all the other types of joins fairly frequently. This connection is written as follows:

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

-- 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 that on an outer join, the inner join records are returned first, followed by the right join records, and finally the left join records (at least, that's how my Microsoft SQL Server does it; this doesn't require the use of any ORDER BY statements, of course). You can visit the Wikipedia article for more information (however, the entry is not graphical). I 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.

insert image description here

This is the end of this article about the specific usage of mysql-joins. For more relevant usage of mysql-joins, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: