Graphic tutorial on SQL Join

SQL Join may be messy during learning. We know that the Join Syntax of SQL has many inner, outer, and left clauses. Sometimes, it is not clear about the result set of Select statements. In an article on Coding Horror (it is not clear why Coding Horror is also walled), Venn diagrams explains SQL Join. I think it's easy to understand.

Suppose we have two tables.

Table A is the Table on the left.
Table B is the right Table.
There are four records, two of which are the same, as shown below:Copy codeThe Code is as follows: 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 ------------ 1 Pirate 2 Pirate 3 Ninja 4 NinjaInner join The result set is the intersection of A and B.
SELECT * FROM TableA Full outer join TableB ON TableA. name = TableB. name Id name ------------ 1 Pirate 2 Pirate 2 Monkey null 3 Ninja 4 Ninja 4 Spaghetti null Null 1 Rutabaga Null 3 Darth VaderFull outer join generates the Union of A and B. However, it should be noted that for records without matching, null is used as the value.
SELECT * FROM TableA Left outer join TableB ON TableA. name = TableB. name Id name ------------ 1 Pirate 2 Pirate 2 Monkey null 3 Ninja 4 Ninja 4 Spaghetti null nullLeft outer join generates A complete set of Table A, while Table B matches with A value. If no match exists, it is replaced by A null value.
SELECT * FROM TableA Left outer join TableB ON TableA. name = TableB. name WHERE TableB. id IS null Id name ------------ 2 Monkey null 4 Spaghetti null is A set that exists in Table A and does not exist 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 ------------ 2 Monkey null 4 Spaghetti null Null 1 Rutabaga Null 3 Darth Vader generates datasets that are not present in both table A and table B.

We also need to register another cross join, which is the "Intersection Set". This Join cannot be represented by a text graph, because it combines the data in tables A and B with N * M, that is, Cartesian product. The expression is as follows:Copy codeThe Code is as follows: SELECT * FROM TableA
Cross join TableB

This Cartesian product generates 4x4 = 16 records. In general, this syntax is rarely used. However, we should be careful that, if we do not use nested select statements, the system will generate Cartesian Product and then filter again. This is very dangerous for performance, especially when the table is large.