oracle02--Multi-Table Association query

1. Multi-Table (association) query

Multi-table query, also known as Association query, multi-table association query, mainly refers to the association of multiple tables to obtain data of a way.

1.1. Multi-table mapping relationships

pair of more: A row of data for table A, corresponding to multiple bars in table B. for example , a department can correspond to multiple employees .

Many-to-one: multiple lines in table B correspond to a row of data in table A. as : multiple employees correspond to one department .

Many-to-many: students and electives ---- students and Course tables .

One-to-one: basic information and personnel information extension table.

1.2. Descartes Set

The effect of the Cartesian set on the data query results of our database:

1. data redundancy. --- Cartesian set is not the data we need .

2. Efficiency issues: lead to an order of magnitude growth . 100w *100w,= = ="1w billion." If you are querying a large amount of data, and you are not paying attention to this Cartesian set, your query results will be very, very long, and will cause a database failure.

Therefore, in the actual operating environment, the use of the full Cartesian set should be avoided .

The conditions produced by the Cartesian set:

L Omit Join conditions

L Invalid connection condition

The following example:

statement One result 3, statement two result 2,

statement Three, whether the addition of invalid connection conditions, or no conditions, the results are 2*3=6, this is due to the Cartesian product problem, so to use effective connection conditions;

How to avoid a Cartesian set:

joins a valid join condition in where.

There are several ways to learn about table correlation at this time.

1.3. types of multi-table joins

depending on how the connection is connected, the types of Oracle's multi-table associations are divided into:

Internal connection, outer connection, self-connection.

The inner connection is divided into: equivalent internal connection, not equivalent internal connection

Outer joins: Left outer connection, right outer connection, full outer connection

A self-join is a special association that can contain both inner and outer connections.

1.4. about the sql99- Understand

Oracle is a relational database that is in compliance with the specification (SQL specification).

However, there are some differences between MySQL and Oracle , because the implementations of each vendor may be different.

Sql99 is intended to standardize the general syntax of multiple relational databases .

1.5. Basic syntax for multi-table joins

syntax for Sql99:

syntax for Oracle:

SQL Statement optimization:

Plus the prefix: High efficiency!

1.6. Internal Connection

1.6.1. Intra-equivalence connection

Equivalent connections are also referred to as equivalent connections.

--* from emp t1,dept T2 WHERE t1.deptno=t2.deptno; --* from emp T1 INNER JOIN Dept T2 on T1.deptno=t2.deptno; --sql99 syntax, showing internal connections (all supported by SQL99 specification) Note: INNER can omit

1.6.2. no equivalent internal connection

Non-equivalent connections are also known as non-equivalent connections.

-- Analysis: To complete this requirement, you need to use the following two tables:---* from emp t1,salgrade T2 WHERE t1.sal >=t2.losal and t1.sal<= T2.hisal; --* from emp INNER JOIN salgrade on Emp.sal >=salgrade.losal and Emp.sal <=salgrade.hisal--sql99

1.6.3. Alias of Table

Why use a table alias?

L Use aliases to simplify queries.

L Use table name prefixes to improve execution efficiency. --sql performance optimization scheme

Columns with the same column names in different tables can be distinguished by using the table's alias as a prefix.

It is important to note that if you use the alias of the table once, you can no longer use the table's true name.

1.6.4. more connections to tables

1.7. External Connection

divided into left outer connection, right outer connection, full outer connection (oracle unique mysql not).

Note: The difference between a single-table query and a multi-table query: A single-table query shows only the data in one table, whereas a multiple-table query typically displays data from multiple tables

As to whether the display is all or part of the field, the content that is queried after select is determined;

Just like the difference between the left and the left: the left outer link shows two table contents, while the left table value shows the contents of the left table

1.7.1. Left outer connection

--Query "All" employee information, request display employee number, name, and department name--* from emp T1 left OUTER JOIN dept T2 on T1.deptno=t2.deptno; --* from emp t1,dept T2 WHERE T1.deptno=t2.deptno (+); --oracle private syntax (MySQL is not supported), + put to the right is left outside, you can think (+) is added to the meaning. a table that asks for all information, which we can call the main table, and a table of supplemental information, called from the table

Note: The outer of the outer connection can be omitted (including left, right, full connection);

1.7.2. Right Outer connection

---to inquire about the information of all departments and employees of their subordinates. --* from emp T1 right OUTER JOIN dept T2 on T1.deptno=t2.deptno; --sql99--right OUTER join--The Right table (dept) data is all displayed. SELECT  * from emp t1,dept T2 WHERE T1.deptno (+) =t2.deptno;--oracle syntax, right outer join

1.7.3. How to choose LEFT outer and right outside

--1. Whether to use left or right outside, mainly to look at the position of the two tables in the statement,--Two table is a master-slave relationship, generally put the main table on the left,---- generally two tables, we use the left connection. SELECT t1. *,t2.* from Dept T1, EMP T2 WHERE T1.deptno=t2.deptno (+); Which side is--2.+ on? Left outer connection + on the right, right outer connection + on left. ----Memory Method: (+) placed on the table from the side, play the role of data attached. The simple: Left outer connection is the left side of the table data are all displayed, right outside the table to the right of the data display. 

Note: This (+) notation can only be used in Oracle. Not for MySQL!

be sure to have Main table and from table This concept distinguishes between the main table and which one is from the table.

Consider the base table you want to query as the left table. If you want to find out who you are, consider it as the main table .

which one is the main table and which one is from the table? Finally, it depends on your needs.

Generally we put the main table on the left and use the left outer connection.

In general, we will use the left join to connect to the line.

1.7.4. Full outer connection

the data for the left table and right tables are all displayed, and are not Cartesian sets. Data equivalent to left outer + right.

Example

Requirements: Require all employees and all departments to be displayed

--* from emp T1 left OUTER JOIN Dept T2 ont1.deptno=   t2.deptnounion--* from emp T1 right J OIN Dept T2 on T1.deptno=T2.deptno; SELECT empno, ename, dname from EMP left JOIN dept on emp.deptno= dept.deptnounion  All--UNION ALL does not Go to Heavy select empno, ename, dname from the EMP Right JOIN dept on emp.deptno= Dept.deptno; --* from emp T1 full OUTER JOIN dept T2 on T1.deptno=t2.deptno;  --Full de ---sql99 syntax, Oracle does not have a private extension syntax. And, MySQL is not all out of the way (MySQL only uses the first two ways, there is no third way)

1.8. self-connect

Self-connection is to query a table as two tables.

1.8.1. Example

the principle of self-connected queries is to use a table as two tables .

Example

1. Query employee information and ask for the names of the leaders of employees and employees at the same time

2. check the "all" employee information and ask for the names of the leaders of the employees and employees .

--* from  emp t1,emp T2 WHERE t1.mgr=t2.empno; --* from  emp t1,emp T2 WHERE t1.mgr=t2.empno (+);

essence: Self -connection is also a special kind of multi-table connection , which can be used in the operation of inner and outer connections.

Note the problem : You should also pay attention to the production of Cartesian sets .

oracle02--Multi-Table Association query