Oracle experience 4 -- set query @ multi-table join in oracle @ Case Study

Oracle experience 4 -- set query @ multi-table join in oracle @ Case study 1. A Brief Introduction to the union of query keywords in a set: union (returns all records of the Two sets after removing duplicate elements) union all (returns all records of the two sets, including duplicate records) intersection: intersect (return records belonging to two sets at the same time) difference set: minus (belongs to the first set, but does not belong to the records of the second set) the parameter type and number in the select statement must be consistent; you can use parentheses to change the sequence in which the set is executed. the header of the first statement is used as the header. The Order by clause can only appear in the last statement, the column name or alias accepted by the first select statement (the column name of the query result) order by ** must be consistent with the column name of the first select Case Analysis -- Union select employee_id, job_id from employees union select employee_id, job_id from job _ History; -- all Union select employee_id, job_id from employees unionall select employee_id, job_id from job_history; -- intersection select employee_id, job_id from employees intersect select employee_id, job_id from job_history; -- select ename of the difference set, sal from emp where sal between700and1300 minus select ename, sal from emp where sal between1201and1400 the query described earlier is limited to a table in the database. However, in practice, we often need to query data in multiple tables or classify and summarize the data in the tables. This requires complicated advanced queries. table relationships: Like mysq, there are three types of table relationships: one-to-many | (Multiple-to-one), multiple-to-many, and one-to-one. integrity constraints of the relationship: entity integrity, referential integrity, and defined integrity. The entity integrity and reference integrity must be met. entity Integrity: Specifies the constraints of the field | attribute. Referential integrity: Constraints and Foreign keys for referencing a field between a link and a link. Note: entity integrity and referential integrity are conditions that must be met by any relational database. User-Defined Integrity: for example, the student's table cannot be older than 60 (user-defined conditions. the connection query in Oracle is the same as that in mysql. There are also self-connection queries and external connections (including left outer connections, right outer connections, and full outer connections) and internal connections (equivalent connections, unequal connections, and natural connections. There is a many-to-one association between the emp table and the dept table (there are other associations in reality). We often want to query more information. In this case, we need to use the connection query. // Query the details of employees and departments, but a Cartesian product is generated. SQL> select * from emp, dept; // how can we avoid Cartesian product? SQL> select * from emp e, dept d where e. deptno = d. deptno; // note the following syntax is problematic: ORA-00918: column SQL not explicitly defined> select e. empno, e. ename, deptno, d. dname from emp e, dept d where e. deptno = d. deptno; Note: deptno exists in both tables, so you must use a prefix to differentiate them. SQL> select e. empno, e. ename, e. deptno, d. dname from emp e, dept d where e. deptno = d. deptno; In summary, the following rules should be observed when creating a connection query: The from clause should include all table names; the where clause should define two join conditions: two tables, one equivalent condition, three tables, and two equivalent conditions... And so on. To connect n tables, at least n-1 join conditions are required. For example, connecting three tables requires at least two join conditions. When the column name is multiple tables, the column name must be limited. Use join query syntax: From table1 join_type table2 on join_condition Join_type join types are classified as follows: inner join is divided into equivalent join according to the comparison method used: use the equal sign (=) operator in the connection condition to compare the column values of the connected columns. All columns in the connected table, including duplicate columns, are listed in the query results. Example: select * from emp e inner join dept d on e. deptno = d. deptno; unequal join: Use a comparison operator other than the equal operator to compare the column values of the connected columns. These operators include >,>=, <=, <,!> ,! <And <> that is! =. Example: select * from emp e inner join dept d on e. deptno> d. deptno; natural join: note the difference between distinguishing and self-join query. Use the equal to (=) operator in the connection condition to compare the column values of the connected column, however, it uses the selection list to indicate the columns included in the query result set and delete duplicate columns in the connection table. Example: select * from emp natural join dept; Note: Distinct removes duplicate rows, while natural join removes duplicate columns. Outer Join: the query results of the inner join are all records that meet the connection conditions. However, sometimes we want to output records that do not meet the connection conditions. For example, if we want to know the situation of all employees in this department, including departments without employees, we need to use external connections. External join only limits the connection conditions for the data in one table, while the data in the other table does not. Three types of external connections: 1) left outer join (left outer join) if all the records in the table on the LEFT are listed in the connection query, if the matching record can be found in the right table, the connection is successful. If no matching record is found in the right table, the corresponding record is NULL ). In this case, the query statement uses the keyword left outer join. That is to say, the meaning of the left outer join is that the data in the table on the right of the JOIN keyword must meet the JOIN conditions, if the data in the left-side table does not meet the connection conditions, the content in the left-side table is output (that is, all the content in the left-side table and the content that meets the conditions in the right-side table ). For example, to query the employee information of all departments, the query statement is SQL> select * from dept d left outer join emp e on e. deptno = d. deptno order by d. deptno; the information of all records in the left end table in the left outer join query is retained. Note: records are retained in the department table. If there are no employees in the Department, the Department shows that the employee records are supplemented with null. 2) right outer join (right outer join) the right outer join is opposite to the left outer join, but all the tuples in the RIGHT end table are listed. The data in the left end table must meet the JOIN conditions, regardless of whether the data in the right table meets the connection conditions, the content in the table is output. For example, in the preceding example, the query statement is SQL> select * from emp e right outer join dept d on e. deptno = d. deptno order by d. deptno; the information of all tuples in the right table in the right outer join query is retained. 3) full outer join (full outer join) the query feature is that records in the left and right tables are output. If no matching record is found, NULL is used instead. For example, the query statement is SQL> select * from emp e full outer join dept d on e. deptno = d. deptno order by d. deptno; the information of the tuples in all tables in the external join query is retained. Cross join: the product of the flute. It refers to any combination of all records in two relations. Generally, cross-query is meaningless. SQL> select * from emp e cross join dept d; Note: you can add a where clause to filter out meaningful data. It is not recommended. Self-join query: if the two tables involved in a connection query are the same table, this query is called self-join query, and the data in the two tables that are naturally connected to the query in the inner, the keyword is natural. The same table appears multiple times in the FROM clause. To differentiate each appearance of the table, you must define an alias for the table. Self-join is a special internal join. It refers to the physical connection of a table to the same table, but it can be logically divided into two tables. For example, You must retrieve the detailed information of the supervisor whose employee number is 7369. The query statement is: select e1. * from emp e inner join emp e1 on e. mgr = e1.empno where e. empno = 7369; Note: inner outer Used in connection query can be omitted. But it is best not to omit the specification. Increase part: the outer join symbol is (+); the plus sign on the left indicates that the conditions are met on the left (the plus sign is one or more meanings !), It is the opposite of the plus sign on the right side. It is the opposite of the left and right sides of the outer join. The left Outer Join is the plus sign on the right side, and the right outer join is the plus sign on the left side; the following is an example :. SQL> select * from emp e inner join dept d on e. deptno (+) = d. deptno; when the natural join clause creates an equijoin, you can use the USING clause to specify the columns to be used in the equijoin. You can use USING to select when multiple columns meet the conditions. Do not add the prefix or alias of the table name to the selected column. Natural join and USING clauses are often used at the same time. SQL> select * from emp e join dept d using (deptno );