For SQL Join, it may be messy to learn. As we know, the Join syntax of SQL has many inner, outer, and left, and sometimes it is a little unclear what the result set of Select looks like. There is an article in Coding Horror (it is not clear why Coding Horror is also blocked) that explains SQL Join via Venn Diagrams. I think it’s clear. Turn around.

Suppose we have two tables.

  • Table A is the Table on the left.
  • Table B is the Table on the right.

Each of them has four records, two of which are identical, as follows:

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   NinjaCopy the code

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    NinjaCopy the code

The result set generated by Inner join is the intersection of A and B.

SELECT * FROM TableA
FULL OUTER JOINTableB 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 VaderCopy the code

Full outer join produces the union of A and B. Note, however, that null is used for records that do not match.

SELECT * FROM TableA
LEFT OUTER JOINTableB ON TableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja  4 Spaghetti null nullCopy the code

The Left outer JOIN produces A complete set of tables A, B, and B, and replaces them 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   nullCopy the code

Produces collections that are present in table A but 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 VaderCopy the code

Generate datasets where neither table A nor table B appears.

We also need to register another “cross set” cross join, which cannot be represented by text graph, because it is to combine the data of table A and table B into an N*M combination, namely, cartesian product. The expression is as follows:

SELECT * FROM TableA
CROSS JOIN TableBCopy the code

This Cartesian product yields 4 x 4 = 16 records, and in general, we rarely use this syntax. But we have to be careful, because if you don’t use nested SELECT statements, the system will produce a Cartesian product and then filter it out. This is very dangerous for performance, especially if the table is very large.

Update: March 30, 2014

(Full text)



Follow CoolShell’s wechat public account to search for articles on your mobile phone

) Please indicate the author and sourceCool shell – CoolShellPlease do not use it for any commercial purpose.)