Multiple table joins in Oracle 4--collection Query @oracle @ Case Analysis ____oracle

1. Simple introduction to the collection query

Key words

Union: Unions (returns all records after two sets remove duplicate elements)

UNION ALL (returns all records of two sets, including duplicates)

Intersection: Intersect (returns records that belong to two collections at once)

Difference set: Minus (records that belong to the first collection but not to the second collection)

The parameter types and numbers in the SELECT statement are consistent; You can use parentheses to change the order in which the collection executes; The collection operation takes the header of the first statement as the header; The ORDER BY clause can only appear on the last statement, the column name or alias that the first SELECT statement accepts (column name of query result) order BY * must be consistent with the column name of the first select

Case analysis

--and set

Select employee_id,job_id from Employees

Union

Select employee_id,job_id from Job_history;

--All and set

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;

--Difference Set

Select Ename,sal from emp where Sal between700and1300

Minus

Select Ename,sal from emp where Sal between1201and1400

The query described earlier is also limited to a table in the database. But in the practical application, we often need to query the data in many tables or need to classify and summarize the data in the table. This requires a more complex, advanced query

2. Table relationship

Relationships between multiple tables: like MYSQ There are three kinds of table relationships : One-to-many | (many to one), many to many, one-on-one;

Integrity constraints on relationships: Entity integrity, referential integrity, and integrity for definition. Entity integrity and referential integrity must be met. Entity integrity : A constraint on the field | property is specified. referential integrity : A constraint, foreign key, that refers to a field between a relationship and a relationship;

NOTE: Entity integrity and referential integrity are conditions that must be met by any relational database.

user-defined integrity : For example: In student form the age of secondary school students cannot be greater than 60 (user-defined conditions)

3. Connection Query

The connection in Oracle is the same as MySQL, there are also self connected query, outer connection (including left outer connection, right outer connection, full outer connection), and internal connection (equivalent connection, unequal connection, natural connection);

There is a many-to-many relationship between the EMP table and the Dept table (there are other associations in the real world), and often we want to query for more information, which means we need to use the connection query.

Query employee and department details but produce a Cartesian product effect.

Sql> select * from Emp,dept;

How to avoid Cartesian product? A comparison of a where query conditional reference relationship

Sql> SELECT * from emp e,dept d where E.deptno=d.deptno;

Note that the following wording is problematic: ORA-00918: Undefined column

Sql> Select E.empno,e.ename,deptno,d.dname from emp e,dept D where E.deptno=d.deptno;

Note: Deptno is present in all two tables, so be sure to use a prefix distinction.

Sql> Select E.empno,e.ename,e.deptno,d.dname from emp e,dept D where E.deptno=d.deptno;

To summarize the following rules should be followed when creating a connection query:

The FROM clause should include all table names; the WHERE clause should define the join condition two table 11 equivalence conditions three tables 2 equivalence conditions ... by analogy. connecting n tables requires at least n-1 a join condition . For example, to connect three tables, you need at least two connection conditions. Column names must be restricted when the column name is common to more than one table.

query using join joins

Syntax: from table1 join_type table2 on join_condition

Join_type connection types are categorized as follows:

Internal Connections :

The inner joins are divided into the inner joins according to the comparison methods used:

equivalent connections : Use the equals sign (=) operator in a join condition to compare the column values of the connected columns, whose query results list all the columns in the connected table, including the repeating columns. Example: SELECT * from emp e INNER JOIN dept D on e.deptno = D.deptno;

Unequal connections : The column values of the connected columns are compared using comparison operators other than the equals operator in the join condition. These operators include >, >=, <=, <,!>,!<, and <>. = Example: SELECT * from emp e INNER JOIN dept D on e.deptno>d.deptno;

Natural Connection : Note the difference between distinguishing from a disconnected query; use the Equals (=) operator in a join condition to compare the column values of the connected columns, but it uses a select list to indicate which columns are included in the query result collection, and deletes duplicate columns from the attached table. Example: SELECT * from EMP Natural JOIN dept;

Note: The distinct is to remove the duplicate rows, while the natural join is to remove the duplicate columns.

outer joins:

The query result for the inner join is a record that satisfies the join condition. However, sometimes we also want to output information about records that do not meet the connection criteria. For example, we want to know the situation of all the employees in this department, and also include departments without employees, then we need to use an external connection. An outer join is one in which only the data in one table must satisfy the join condition, and the data in the other table can not meet the connection conditions. 3 Kinds of Outer connection:

　　1 Left OUTER join (OUTER join)

If all the records in the table on the left side of the join table are listed in the connection query, and a matching record can be found in the table at the right end, the connection succeeds. If a matching record is not found in the right side of the table, the corresponding record is null (NULL). At this point, the query statement uses the keyword left OUTER join, which means that the outer join means that the data in the table at the right end of the connection keyword must satisfy the join condition, and that the data in the left-side table does not meet the join condition, and outputs the contents of the left table ( That is, the output of the table on the left and the part of the right table that satisfies the condition .

For example, to query the employee information for 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 for all records in the left table in the left OUTER join query is preserved.

Note: Records in the departmental table are maintained, and if there are no employees in the department, the department shows that the employee records are supplemented with null.

　　2) right outer join (right-hand OUTER join)

The right outer join is the opposite of the left outer join, except that all tuples in the right side table are listed, and the data that restricts the left table must satisfy the join condition, and the contents of the table are output regardless of whether the data in the right table satisfies the join condition.

For example: In the previous example, the query statement is

Sql> SELECT * from EMP e right outer join Dept D in E.deptno=d.deptno Order by D.deptno;

The information for all tuples in the right side table in the right outer join query is preserved.

　　3) All-in-outer connection (full OUTER join)

A full outer join query is characterized by the output of records from both the left and right tables, and NULL instead if a matching record is not found.

For example: the same left OUTER join example content, the query statement is

Sql> SELECT * from emp e full outer JOIN Dept D in E.deptno=d.deptno Order by D.deptno;

The tuple information in all tables in a full outer join query is preserved.

Cross join:

A cross connection is a Descartes product, which refers to any combination of records in two relationships. In general, there is no practical meaning to cross queries.

Sql> SELECT * from emp e cross join Dept D;

Note: You can add a WHERE clause to filter out meaningful data. recommended not used .

self-connected query:

If in a connection query, the two tables involved are the same table , which is called a natural query, and the two table data in the natural join query, the key word is a. The same table appears multiple times in the FROM clause, and to distinguish each occurrence of the table, you need to define an alias for the table. A self-connection is a special kind of inner join , which means that the linked table is physically the same table, but it can be logically divided into two tables.

For example, the request retrieves the details of the boss with the employee number 7369, the query statement is: SELECT e1.* from emp e INNER JOIN EMP E1 on E.mgr=e1.empno where e.empno=7369;

Note: The inner outer used in the join query can be omitted. However, it is best not to omit the specification.

Improvement section:

the symbol for the outer join IS (+), and the left write plus sign indicates that the left satisfies the condition (the plus sign is one or more meanings). ), and all on the right, and the right side write the plus sign, which is exactly the opposite of the outer join, and the left outer join is to write the plus sign on the right, and the right outer join is to write the plus sign on the left, to be aware of it; The following example:.

Sql> SELECT * from emp e INNER JOIN Dept D on E.deptno (+) =d.deptno;

when you create an equivalent connection in the natural join clause, you can use a using clause to specify the columns that are needed in an equivalent connection . Use using allows you to select when more than one column satisfies a condition. Do not add a table name prefix or alias to the selected column. NATURAL joins and using clauses are often used concurrently.

Sql> SELECT * from emp e join Dept D using (DEPTNO);