Union of SQL statements, join of intersection (inner join, Outer Join), cross join Cartesian Product, difference set (not in)

1.

A. Union select column1, column2 from Table1 Union select column1, column2 from Table2

B. Join the intersection select * From Table1 as a join Table2 B on A. Name = B. Name

C. difference set not in select * From Table1 where name not in (Select name from table2)

D. Cartesian product select * From Table1 cross join Table2 and select * From Table1, Table2 are the same

2.

The difference between Union and Union all in SQL is that the former removes duplicate entries and the latter retains them.

A. Union SQL statement1 Union SQL statement2

B. Union all SQL statement1 Union all SQL statement2

3.

Various join SQL statements in SQL can be divided into inner join, outer join, and cross join (that is, Cartesian Product)

A. Cross join if the WHERE clause is not included, it returns the Cartesian product of the two joined tables.The number of rows in the returned results is equal to the product of the number of rows in two tables. For example, select * From Table1 cross join Table2 is equivalent to select * From Table1, Table2 is generally not recommended, because if a where clause existsData Tables of rows that multiply by the number of rows in two tables are usually created based on whereComponent. Therefore, if two tables that require communication are too large, they will be very slow and are not recommended.

B. Join inner join. If only select * From Table1 inner join Table2 is used, the join condition is not specified, which is the same as that of cross join. However, when using inner join, you must specify the connection conditions. -- Equivalent join (= applies to join conditions without removing duplicate columns) Select * From Table1 as a inner join Table2 as B on. column = B. column -- unequal connection (>,>=,<,<=,!> ,! <, <>) For example, select * From Table1 as a inner join Table2 as B on A. Column <> B. column -- Natural join (duplicate columns are removed)

C. Outer Join outer join the difference between the inner join and outer join: if the inner join does not specify the join condition, the result of the cross join is the same as that of the Cartesian product. But what is different from Cartesian product is that it is not as complicated as Cartesian product. The efficiency of inner join is higher than that of Cartesian data tables. Only entries that meet the connection conditions are returned. The outer join is different, and the returned results not only contain rows that meet the connection conditions And includes all data rows in the left table (when left Outer Join), right table (when right join), or two sides (when all outer joins. 1) left Outer Join left [outer] Join shows the qualified data rows, and the left data table does not meet the conditions. , No corresponding entries on the right show null. 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) all external connections display data rows that meet the conditions and left and right data rows that do not meet the conditions , The corresponding left and right sides show null