SQL statement UNION intersection JOIN (inner JOIN, Outer JOIN) and so on, unionjoin

SQL statement UNION intersection JOIN (inner JOIN, Outer JOIN) and so on, unionjoin
1. a. union select column1, column2 FROM table1 union select column1, column2 FROM table2b. intersection join select * FROM table1 AS a JOIN table2 B ON. name = B. namec. 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 is 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 Statement2b. in union all SQL Statement1 union all SQL Statement23. SQL, connections in various join SQL statements can be divided into inner JOIN, outer JOIN, and cross JOIN (that is, Cartesian Product). if the cross join clause does not contain the WHERE clause, it returns the Cartesian product of the two joined tables. The number of rows returned is the product of the number of rows in the two tables; for example, SELECT * FROM table1 cross join table2 is equivalent to SELECT * FROM table1. This method is not recommended for table2 because if a WHERE clause exists, the data table is usually the data table of the rows that multiply the number of rows in 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. 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. column <> B. column -- Natural join (removing duplicate columns) c. outer join first has the difference between the inner JOIN and the 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, however, unlike cartesian products, the internal join efficiency is higher than that of cartesian products for data tables that are not as complex as cartesian products, which require the product of numbers in rows. Only entries that meet the connection conditions are returned. 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 shows 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 the qualified data rows, and the left and right data rows that do not meet the conditions are displayed. The corresponding left and right data rows are displayed as NULL.