Illustration: mysql join on, SQL join, and database SQL join statements

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.

• TableIs the table on the left.
• Table BIs the table on the right.

There are four records, two of which are the same, as shown below:

id name       id  name-- ----       --  ----1  Pirate     1   Rutabaga2  Monkey     2   Pirate3  Ninja      3   Darth Vader4  Spaghetti  4   Ninja

Let's take a look at the results of different joins.

 

SELECT * FROM TableAINNER JOIN TableBON TableA.name = TableB.nameid name id name-- ---- -- ----1 Pirate 2 Pirate3 Ninja 4 Ninja Inner join The result set is the intersection of A and B.
SELECT * FROM TableAFULL OUTER JOIN TableBON TableA.name = TableB.nameid name id name-- ---- -- ----1 Pirate 2 Pirate2 Monkey null null3 Ninja 4 Ninja4 Spaghetti null nullnull null 1 Rutabaganull null 3 Darth Vader Full outer joinGenerate the Union of A and B. However, it should be noted that for records without matching, null is used as the value.
SELECT * FROM TableALEFT OUTER JOIN TableBON TableA.name = TableB.nameid name id name-- ---- -- ----1 Pirate 2 Pirate2 Monkey null null3 Ninja 4 Ninja4 Spaghetti null null Left outer joinGenerates 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 TableALEFT OUTER JOIN TableBON TableA.name = TableB.nameWHERE TableB.id IS null id name id name-- ---- -- ----2 Monkey null null4 Spaghetti null null Generate A set that exists in Table A but not in table B.
SELECT * FROM TableAFULL OUTER JOIN TableBON TableA.name = TableB.nameWHERE TableA.id IS null OR TableB.id IS null id name id name-- ---- -- ----2 Monkey null null4 Spaghetti null nullnull null 1 Rutabaganull null 3 Darth Vader Generate A dataset that does not appear in both table A and table B.

We also need to register another one: the delivery set"Cross joinThere is no way to use the style graph for this Join, because it is to combine the data of table A and table B with N * M, that is, Cartesian product. The expression is as follows:

SELECT * FROM TableACROSS 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.

(Full text)