Shulou(Shulou.com)06/01 Report--

What is multi-table query? A multi-table query is to get data from multiple tables. 1. Cartesian set Cartesian set is a kind of set. Suppose that An and B are sets, and the Cartesian sets of An and B are represented by A. That is, the set formed by A B is called the Cartesian set. In the following table, the department sheet is the Cartesian set of the department table and the employee table. It can be seen that three items of data in the department schedule are incorrect. )

The an and UNIONUNION operators are used to obtain the union of two result sets. When you use this operator, duplicate rows in the result set are automatically removed and sorted in ascending order with the results of the first column. Example: combine to display the department number, employee number, and employee name of all employees in the emp02 table and the emp02 table. SQL > create table emp01 as select * from emp where deptno in (10pm 20); the table has been created. SQL > create table emp02 as select * from emp where deptno in (200.30); the table has been created. SQL > select deptno, empno, ename from emp01 union select deptno, empno, ename from emp02 DEPTNO EMPNO ENAME 10 7782 CLARK 10 7839 KING 10 7934 MILLER 20 7369 G_EASON 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 20 7951 EASON 30 7499 ALLEN 30 7521 WARD 30 7654 MARTIN 30 7698 BLAKE 30 7844 TURNER 30 7900 JAMES 15 rows have been selected. The b and UNION ALLunion all operators are used to obtain the union of two result sets, unlike the union operator, which does not unrepeat rows and does not sort the result set data. SQL > select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02; DEPTNO EMPNO ENAME 20 7951 EASON 20 7369 G_EASON 20 7566 JONES. 30 7844 TURNER 20 7876 ADAMS 30 7900 JAMES 20 7902 FORD 21 rows have been selected. The c and INTERSECTintersect operators are used to obtain the intersection of two result sets. When used, only the data that exists in both result sets is displayed, and the results of the first column are sorted in ascending order. SQL > select deptno, empno, ename from emp01 intersect select deptno, empno, ename from emp02; DEPTNO EMPNO ENAME 20 7369 G_EASON 20 7566 JONES 20 7788 SCOTT 20 7876 ADAMS 20 7902 FORD 20 7951 EASON has selected 6 rows. The d and MINUSminus operators are used to get the difference between the two result sets. When you use this operator, only the data that exists in the first result set and does not exist in the second result set is displayed, and the result set of the first column is sorted in ascending order. SQL > select deptno, empno, ename from emp01 minus select deptno, empno, ename from emp02; DEPTNO EMPNO ENAME 10 7782 CLARK 10 7839 KING 10 7934 MILLERe, Control result sorting when using the set operators UNION, INTERSECT, and MINUS, ascending sort is automatically based on the first column by default Instead of sorting when using the collection operator UNION ALL, you can use the ORDER BY clause to control the sort order of the results. (if the column names in the results of the two table queries are the same, you can use the column name If the column names in the two tables are different, you must use the column location, 1 for column 1, 2 for column 2) example: select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by 2 SQL > select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by empn DEPTNO EMPNO ENAME 20 7369 G_EASON 20 7369 G_EASON 30 7499 ALLEN 30 7521 WARD 20 7566 JONES. 20 7902 FORD 10 7934 MILLER 20 7951 EASON 20 7951 EASON 21 rows have been selected. SQL > select deptno, empno, ename from emp01 union all select deptno, empno, ename from emp02 order by DEPTNO EMPNO ENAME 20 7369 G_EASON 20 7369 G_EASON 30 7499 ALLEN 30 7521 WARD. 20 7902 FORD 10 7934 MILLER 20 7951 EASON 20 7951 EASON 21 rows have been selected. F, considerations for using the set operator the column names and expressions in the SELECT list should correspond in quantity and data type.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.