For SQL joins, learning may be a bit confusing. We know that the join syntax for SQL has a lot of inner, outer, left, and sometimes it's not very clear what the result set looks like for a select. There is an article on Coding horror that explains the join of SQL through the Venn diagrams of the Venturi diagram. I feel clear and understandable, turn around.
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 (intersection)
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 (set)
(1) SELECT * from TableAFull
OUTER JOIN TableB 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 TableALeft
OUTER JOIN TableB 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 |
Left OUTER JOINThe full set of Table A is generated, and the match in table B has a value, and no match is substituted with a null value. (2) SELECT * from TableALeft
OUTER JOIN TableB in 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.UNION And
UNION AllThe UNION operator is used to combine the result set of two or more SELECT statements.
Notice that the SELECT statement inside the UNIONyou must have the same number of columns. Columns must also have similar data types。 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
UNION SELECT name from TableB
New result set |
Name |
Pirate |
Monkey |
Ninja |
Spaghetti |
Rutabaga |
Darth Vade |
Choose a different value (2) select name from TableA
UNION all SELECT name from TableB
New result set |
Name |
Pirate |
Monkey |
Ninja |
Spaghetti |
Rutabaga |
Pirate |
Darth Vade |
Ninja |
All listed.
(3) Note:
SELECT * from TableA
UNION SELECT * 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. Reprinted from: http://blog.csdn.net/mangmang2012/article/details/8017034
Various joins of SQL