Diagram of the difference between SQL inner join, left JOIN, right join, full outer join, Union, UNION ALL

Transferred from: http://blog.csdn.net/jz20110918/article/details/41806611

Let's say we have two tables. Table A is the sheet on the left. Table B is the list on the right. Each of them has four records, of which two records name is the same, as follows: Let's look at the different joins

Table A
Id	Name
1	Pirate
2	Monkey
3	Ninja
4	Spaghetti

b table
Id	Name
1	Rutabaga
2	Pirate
3	Darth Vade
4	Ninja

1.INNER JOIN

SELECT * from TableA INNER JOIN TableB on tablea.name = Tableb.name

Result set
(TableA.)		(TableB.)
Id	Name	Id	Name
1	Pirate	2	Pirate
3	Ninja	4	Ninja

The result set produced by the Inner join is the intersection of a and B.

2.FULL [OUTER] JOIN(1) SELECT * from TableA Full OUTER JOINTableB on tablea.name = Tableb.name Col span= "4" width= "$" >

result set
(TableA.)
ID	name	ID	name
1	Pirate	2	Pirate
2	Monkey	null	null
3	Ninja	4	Ninja
4	Spaghetti	NULL	NULL
null	null	1	rutabaga
null	null	3	Darth vade

The full outer join produces a and B's set. It is important to note, however, that for records that do not have a match, NULL is the value. Can be judged using ifnull. (2) SELECT * from TableA Full OUTER JOINTableB on tablea.name = Tableb.name
WHERE tablea.id is null OR tableb.id is null

Result set
(TableA.)		(TableB.)
Id	Name	Id	Name
2	Monkey	Null	Null
4	Spaghetti	Null	Null
Null	Null	1	Rutabaga
Null	Null	3	Darth Vade

produces datasets that have no intersection between A and B tables. 3.LEFT [OUTER] JOIN(1) SELECT * from TableA Left OUTER JOINTableB on tablea.name = Tableb.name

Result set
(TableA.)		(TableB.)
Id	Name	Id	Name
1	Pirate	2	Pirate
2	Monkey	Null	Null
3	Ninja	4	Ninja
4	Spaghetti	Null	Null

The left outer join produces a full set of table A, whereas a match in B table has a value, and no match is substituted with a null value. (2) SELECT * from TableA Left OUTER JOINTableB on tablea.name = Tableb.name WHERE tableb.id is null

Result set
(TableA.)		(TableB.)
Id	Name	Id	Name
2	Monkey	Null	Null
4	Spaghetti	Null	Null

Produces a collection that is available in table A and not in the B table.

4.RIGHT [OUTER] JOINRight OUTER  Join is based on the following table, similar to the left OUTER join usage. This is not covered here. 5.UNIONAnd UNION AllThe UNION operator is used to combine the result set of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same. Union selects only records, and union all lists all records. (1) SELECT name from TableA UNIONSELECT name from TableB

New result set

Name

Pirate

Monkey

Ninja

Spaghetti

Rutabaga

Darth Vade

Choose a different value (2) select name from TableA UNION AllSELECT name from TableB

New result set

Name

Pirate

Monkey

Ninja

Spaghetti

Rutabaga

Pirate

Darth Vade

Ninja

All listed.

(3) Note:

SELECT * from TableA UNIONSELECT * from TableB

New result set
Id	Name
1	Pirate
2	Monkey
3	Ninja
4	Spaghetti
1	Rutabaga
2	Pirate
3	Darth Vade
4	Ninja

Since ID 1 Pirate is not the same as ID 2 Pirate, it is also necessary to register for non-merging we also have a "cross set" cross join, which has no way to be represented by Wenshitu because it is a combination of a n*m of the data of table A and table B, that is, the Cartesian product. The expression is as follows: SELECT * from TableA cross JOIN TableB This Cartesian product produces 4 x 4 = 16 records, which, in general, are seldom used in this syntax. But we have to be careful, if you do not use nested SELECT statements, the general system will produce a Cartesian product and then filter. This is very dangerous for performance, especially when the table is very large.

The difference between the inner join, left JOIN, right join, full outer join, Union, UNION All of the graphical SQL