In the case of SQL joins, it may be a bit messy to learn. We know that the SQL join syntax has a lot of inner, outer, left, and sometimes it's not clear what the result set of the select is like. An article on coding horror (it's not clear why coding horror also been walled) explains SQL joins through the Venn diagrams of the graph. I think it's easy to understand, turn around.
Let's say we have two tables.
Table A is the tables on the left.
Table B is the tables on the right.
Each has four records, of which two records are the same, as follows:
Copy 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 take a 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 the set of A and B. It should be noted, 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 outer JOIN produces a full set of table A, while the matches in table B have values, and no matches are replaced with null values.
|
|
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 produces a set in table A 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
A null NULL 3 Darth Vader produces a dataset that does not appear in table A and B.
|
|
Also need to register is that we also have a "cross" join, this join has no way to use the Wenshitu, because it is the table A and table B data for a n*m combination, that is, Cartesian product. The expression is as follows:
Copy Code code as follows:
SELECT * from TableA
CROSS JOIN TableB
This Cartesian product produces 4 x 4 = 16 records, and in general we rarely use this syntax. But we have to be careful that if you don't use nested SELECT statements, the general system will produce the Cartesian product and then filter it. This is very dangerous for performance, especially when the table is very large.
