Oracle 4-set Query

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) unionall (returns all records of the two sets, including duplicate records) intersection: intersect (return records belonging to both sets) difference set: minus (belongs to the first set but does not belong to the records of the second set) select language

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 both sets) difference set: minus (belongs to the first set but does not belong to the records of the second set) select language

1. A brief introduction to set Query

Keywords

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

Union all (returns all records of two sets, including duplicate records)

Intersection: intersect (returns records belonging to both sets)

Difference set: minus (Records of the first but not the second set)

The type and number of parameters in the select statement must be the same. Brackets can be used to change the sequence of execution of the set. the header of the first statement is used as the header of the set operation; 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 statement.

Case Analysis

-- Union

Select employee_id, job_idfrom employees

Union

Select employee_id, job_idfrom job_history;

-- All Union sets

Select employee_id, job_idfrom employees

Unionall

Select employee_id, job_idfrom job_history;

-- Intersection

Select employee_id, job_idfrom employees

Intersect

Select employee_id, job_idfrom job_history;

-- Difference set

Select ename, salfrom empwhere salbetween700and1300

Minus

Select ename, salfrom empwhere salbetween1201and1400

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 complex advanced queries.

2. Table relationship

Relationship between multiple tables: Same as mysqThree table relationships: One-to-many | (many-to-one), many-to-many, one-to-one;

Integrity constraints of a link: entity integrity, referential integrity, and defined integrity. Entity integrity and reference integrity must be met.Entity integrity: Specifies the FIELD | attribute constraints.Integrity of reference: Constraints and Foreign keys for referencing a field between a link and a link;

Note: entity integrity and reference integrity are conditions that must be met by any relational database.

User-Defined integrity: For example, in the student table, the student's age cannot be greater than 60 (user-defined conditions)

3. Connection Query

Like mysql, Oracle connections also have 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? Join the where query condition reference relationship comparison

SQL> select * from emp e, dept d where e. deptno = d. deptno;

// Note that the following statements are problematic: ORA-00918: columns not explicitly defined

SQL> 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 followed when creating a connection query:

The from clause should include all the table names. The where clause should define the join conditions, two tables, one equivalent condition, three tables, and two equivalent conditions... And so on.Connecting n tables requires at least n-1 connection conditions.. For example, connecting three tables requires at least two join conditions. When the column name is multiple tables, the column name must be limited.

Join Query

Syntax: From table1 join_type table2 on join_condition

Join_type connection types are classified as follows:

Internal Connection:

The internal connection is divided:

Equijoin: Use the equal sign (=) operator in the connection condition to compare the 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 connection: Use a comparison operator other than the equal operator to compare the values of the joined columns in the join condition. These operators include >,>=, <=, <,!> ,! <和<> That is! =. Example: select * from emp e inner join dept d on e. deptno> d. deptno;

Natural connection: Note the difference between distinguishing and self-join queries. Use the equal to (=) operator in the connection condition to compare the column values of the connected columns, 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.

External Connection:

The query results of internal connections 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)

If all the records in the left-end table of the connection table are listed in the connection query and matched records can be found in the right-end 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-end table does not meet the connection conditions, the content of the left-end table is output (That is, all the left and right tables that meet the conditions are output.).

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)

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 end table meets the join conditions, all output table content.

For 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 query is characterized by the output of records in both the left and right tables. If the matching records cannot be 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 all outer join query is retained.

Cross join:

A crossover is the product of a flute. It refers to any combination of all records in two relationships. Generally, the cross query isNo practical significance.

SQL> select * from emp e cross join dept d;

Note: you can add a where clause to filter out meaningful data.Not recommended.

Self-connection query:

If the two tables involved in a connection query areSame tableThis type of query is called a self-join query, and the data in the two tables that are queried by the inner natural connection, 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-connection is a special internal connection.It refers to the physically the same table as a joined 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.

Enhancement part:

The External Connection Symbol is (+); The plus sign on the left indicates that the condition is 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 creating an equijoin in the natural join clause, 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 );