Join use diagram for SQL

Transferred from: http://blog.csdn.net/koudaidai/article/details/7901058

——————————————————————————————————————

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 (it is not clear why Coding horror was also the wall) through the Venturi diagram Venn diagrams explained the join of SQL. 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 are the same, as follows:

Copy the code code as follows:
ID Name ID Name
-- ---- -- ----
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja

Let's look at the results of different joins.

SELECT * from TableA INNER JOIN TableB On tablea.name = Tableb.name ID Name ID Name -- ---- -- ---- 1 Pirate 2 Pirate 3 Ninja 4 Ninjainner Join The resulting set of results is the intersection of a and B.
SELECT * from TableA Full OUTER JOIN TableB On tablea.name = Tableb.name 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 vaderfull 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.
SELECT * from TableA Left OUTER JOIN TableB On tablea.name = Tableb.name ID Name ID Name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey NULL NULL 3 Ninja 4 Ninja 4 spaghetti NULL Nullleft the 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.
SELECT * from TableA Left OUTER JOIN TableB On tablea.name = Tableb.name WHERE tableb.id is null ID Name ID Name -- ---- -- ---- 2 Monkey NULL NULL 4 spaghetti NULL NULL is generated in a table that has a collection that is not in table B.
SELECT * from TableA Full OUTER JOIN TableB On tablea.name = Tableb.name WHERE tablea.id is null OR tableb.id is null ID Name ID Name -- ---- -- ---- 2 Monkey NULL NULL 4 spaghetti NULL NULL NULL NULL 1 Rutabaga NULL NULL 3 Darth Vader produces datasets that do not appear in both A and B tables.

It is also necessary to register that we also have a cross join of "cross-set", which is not represented by Wenshitu because it is a n*m combination of the data of table A and table B, that is, the Cartesian product. The expression is as follows:

Copy the code code 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.

Join use diagram for SQL