Various connection usages for SQL (cross join, INNER join, full join)

1. Noun Explanation:

Cartesian product: The Cartesian product is defined as a Cartesian product of two sets X and Y in mathematics, also called a direct product, which represents x x Y, the first object is a member of X, and the second object is a member of all possible sequences of Y. Suppose set a={a,b}, set b={0,1, 2}, then two Cartesian product {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}

2. Original site reference: http://ashui.net/archives/2013/552.html

1> Cross Join

SELECT * FROM table1 cross JOIN table2

Without a WHERE clause, it will return the Cartesian product of the two tables connected, and the number of rows returning the result is equal to the product of two table rows;

SELECT * FROM table1 a cross join Table2 b where a.id=b.id (note: After cross join conditions can only be used where, not on)

2> Connection INNER JOIN

SELECT * FROM table1 INNER JOIN table2

If the connection condition is not specified, it is the same as the cross connection of the Cartesian product, but unlike the Cartesian product, the data table of the product of the number of rows of the Cartesian product is not so complicated that the inner connection is more efficient than the cross-connection of the Cartesian product.

SELECT * FROM table1 as a INNER joins Table2 as B on A.column=b.column

3> outer JOIN outer JOIN

1) left outer connection [OUTER] Join

SELECT * FROM table1 as a left [OUTER] JOIN on A.column=b.column

2) Right-side connection-[OUTER] Join

SELECT * FROM table1 as a right [OUTER] JOIN on A.column=b.column

3) Full-outer connection complete [outer] Join

Displays rows of data that match the criteria, displaying both left and right rows of data that are not eligible, showing null on both sides of the row, which shows the left join of the connection, the correct join, and the INNER join

3.cross join usage, the case that is actually used in the project.

SELECT * from
     (
Select ' Int0 ' as PV from XXX
where Hdfs_par = ' 20170525 ' and target_key= ' Zaixinkefu_faq '
) T1
Cross JOIN
    (
Select Max (' Int0 ') as MAX_PV from XXX
where hdfs_par between ' 20170519 ' and ' 20170525 '
and target_key= ' Zaixinkefu_faq '
) T2

Running Result: PV,MAX_PV

Various connection usages for SQL (cross join, INNER join, full join)