Diagram of the difference between SQL inner join, left JOIN, right join, full outer join, Union, UNION ALL

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 that explains the join of SQL through the Venn diagrams of the Venturi diagram. 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 name is the same, as follows: Let's look at the different joins

1.INNER JOIN

SELECT * from TableA INNER JOIN TableB on tablea.name = Tableb.name

2.FULL [OUTER] JOIN

(1)

SELECT * from TableA full OUTER JOIN TableB on tablea.name = Tableb.name

4.RIGHT [OUTER] JOIN

The right OUTER join is based on the following table, similar to the left OUTER join usage. This is not covered here.

5.UNION and UNION all

The UNION operator is used to combine the result set of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same. Union selects only records, and union all lists all records.

(1) Select name from TableA UNION SELECT name from TableB

Choose a different value

(2) Select name from TableA UNION all SELECT name from TableB

All listed.

(3) Note:

SELECT * from TableA UNION SELECT * from TableB

Because ID 1 Pirate is not the same as ID 2 Pirate, it does not merge

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: 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.

Reference: http://blog.diyiye.com/?post=10