SQL join usage (full, left, out, inner)

SQL join usage (full, left, out, inner)

A. Cross join Cartesian product if there is no where Condition Clause, it will return the Cartesian product of the two joined tables, and the number of rows returned is equal to the product of the number of rows of the two tables; For example, the execution results of A, B, and C are the same but the efficiency is different: A: Select * From Table1 cross join Table2 B: Select * From Table1, Table2 C: Select * From Table1 a inner join Table2 B A: select. *, B. * From Table1 A, Table2 B where. id = B. id B: Select * From Table1 a cross join Table2 B where. id = B. ID (Note: Only the WHERE clause can be used for cross join, not on) C: Select * From Table1 a inner join Table2 B on. id = B. ID It is generally not recommended to use methods A and B, because if there is a where clause, the data table is usually formed into the data table of the rows of the two tables, and then selected based on the where condition. Therefore, if two tables that require communication are too large, they will be very slow and are not recommended. B. Combination of inner join tables on both sides meeting both conditions If you only use select * From Table1 inner join Table2 If no join condition is specified for an inner join, the result of the cross join is the same as that of the Cartesian product. However, unlike the Cartesian product, the data table is not as complex as the Cartesian product, the internal join efficiency is higher than that of cartesian products. However, when using inner join, you must specify the connection conditions. * ************** About equivalent connections and natural connections Equivalent join (= applies to join conditions without removing duplicate columns) Natural join (duplicate columns are removed) Database Connection operations are all natural connections because duplicate rows (tuples) are not allowed. For example: Select * From Table1 as a inner join Table2 as B on A. Column = B. Column C. The outer join only returns the entries that meet the connection conditions for the specified inner join condition. The outer join is different. The returned results include not only the rows that meet the connection conditions, but also the left table (when the left Outer Join is performed) and the right table (when the right join is performed) or all data rows connected by both sides (when all external connections. 1) left Outer Join left [outer] Join The data rows that meet the conditions are displayed, and the data rows that do not meet the conditions in the left data table are displayed. No corresponding entries on the right are displayed. For example, select * From Table1 as a left [outer] join on. column = B. column 2) Right outer join right [outer] Join shows the qualified data rows and the data rows that do not meet the conditions in the right data table, no corresponding entries on the left show null. For example, select * From Table1 as a right [outer] join on. column = B. column 3) Full outer join full [outer] Join shows the qualified data rows, and displays the left and right data rows that do not meet the conditions. The corresponding left and right data rows are null, displays the Union of left, right, and inner connections.

Trackback: http://www.blogjava.net/algz/articles/228218.html