Oracle Multi-table queries (1)

First, the basic concept

The syntax for a multi-table query is as follows:

SELECT [DISTINCT] * | field [Alias] [, Field [alias],...] from table name [alias], [table name [alias],...] [WHERE condition (S)] [Order by Sort field [asc| DESC] [, sort field [asc| DESC],...];

However, if you want to make a multi-table query, you first have to query out several data-the amount of data in the employee and departmental tables, which can be done through the count () function.

Example: querying the amount of data in an EMP table--14 Records returned

SELECT COUNT (*) from EMP;

Example: querying the amount of data in a Dept table--4 records

SELECT COUNT (*) from dept;

add a little extra: What is experience?

In future development, many people are sure to have access to a number of new databases and data tables, then there are two ways:

• Procedure one: New practice, come up directly into the following commands:

SELECT * from table name;

If at this time the amount of data is large, one can not browse the data, two may cause the system to panic;

• Procedure two: Old people practice, first look at how many records:

SELECT COUNT (*) from table name;

If you have a small amount of data at this point, you can query all of the data and you cannot use the Select query directly if the amount of data is large.

Now that you have determined the records in the EMP and Dept tables, a basic multi-table query is completed below:

SELECT * from EMP, dept;

But now the query found that a total of 56 records = 14 Records of the employee table * 4 Records of the department table, the cause of this problem is mainly determined by the query mechanism of the database, for example, as shown in.

This problem is called the Cartesian product in the operation of the database, it means the product of the data of multiple tables, but the result of this query is certainly not what the user wants, so how to remove the Cartesian product?

The simplest way is to take the form of an associative field, where there is now a deptno associated field between the EMP table and the Dept table, so now you can start with the judgment on this field.

When in a query, the same field names are in different tables, access to these fields must be given the table name, which is "table. Field".

SELECT * from Empwhere Emp.deptno=dept.deptno;

At this point in the query results have been eliminated Cartesian product, but now only belong to the elimination of the display, and the real Cartesian product is still present, because the database operation mechanism belongs to row by line to make the judgment of the data, then if according to this idea to understand, now assume that the data of the two tables is very large, Then use the performance of this multi-table query.

Example: a large data table for SH users

SELECT COUNT (*) from sales, Costswhere sales.prod_id=costs.prod_id;

These two tables even eliminate the Cartesian product display, but it will also have Cartesian product problems, so the final query results will be very slow display, or even do not show, so through this program must remember that the performance of the multi-table query is very poor, of course, poor performance is a prerequisite: data volume is large.

However, the above program also has a problem, in the previous access to the table field in the use of "table. Field" Name, then if it is assumed that the table name is very long, such as "Yinhexi_diqiu_yazhou_zhongguo_beijing_xicheng_ren", Therefore, generally in the case of multi-table query is often an alias for the table, through the alias. The way the field is queried.

SELECT * from EMP E, dept dwhere E.deptno=d.deptno;

Example: querying each employee's number, name, position, department name, location

1. Determine the required data sheet:

• EMP Table: Can find out the employee's number, name, position;

• Dept Table: Can query out the department name and location;

2, determine the table's associated fields: Emp.deptno=dept.deptno;

First step: Find out the number, name, and position of each employee

SELECT E.empno, E.ename, E.jobfrom emp e;

Step Two: introduce the departmental table to the query and add a condition to eliminate the Cartesian product

SELECT E.empno, E.ename, E.job, D.dname, D.locfrom emp E, dept, Dwhere E.deptno=d.deptno;

After encountering the problem, found that there is no problem-solving ideas, follow the above steps, and slowly analyze the solution, because the multi-table query can not be written all at once, need to step-up analysis.

Example: ask for the name, position, and leader name of each employee.

Now you must prepare two EMP tables, so this time can be called the EMP Table of its own association, according to the previous analysis as follows:

1. Determine the required data sheet:

• EMP Table (Employee): Obtain the employee's name, position, leader number;

• EMP Table (Leader): Obtain the employee's name (leader's name);

2. Determine the associated field: Emp.mgr=memp.empno (Employee's leader Number = employee number of the leader (employee))

The first step: query Each employee's name, position

SELECT e.ename, E.jobfrom emp e;

The second step: Query the leader information, add their own association

SELECT E.ename, E.job, M.enamefrom emp E, emp mwhere e.mgr=m.empno;

There is a lack of "king" in the query results, because King does not lead, and to solve this problem, you need to wait to explain the left and right connection problem.

Example: find the number, name, base salary, position, name of the leader, department name, and location of each employee.

1. Determine the required data sheet:

• EMP table: Number, name, base salary, position of each employee;

• EMP Table (Leader): The name of the leader;

• Dept Table: The name and location of the department.

2. Determine the known associated fields:

• Employees and departments: Emp.deptno=dept.deptno;

• Employees and leaders: Emp.mgr=memp.empno;

First step: Find out the number, name, base salary, position of each employee

SELECT empno, ename, Sal, Jobfrom EMP;

Step Two: join the leader's information, introduce their own association, and increase the elimination of Cartesian product conditions

SELECT E.empno, E.ename, E.sal, E.job, M.enamefrom emp E, emp mwhere e.mgr=m.empno;

Step three: join the Department of Information, introduce dept table, since there are new tables come in, you need to continue to increase the elimination of Cartesian product conditions

SELECT E.empno, E.ename, E.sal, E.job, M.ename, D.dname, D.locfrom emp E, EMP m, dept dwhere E.mgr=m.empno and E.deptno=d. Deptno

Therefore, all the similar problems in the future can best be written in the manner described above, forming their own ideas.

Study questions: now ask for the number, name, salary, department name, and salary level of the company in which each employee is located.

1. Determine the required data sheet:

• EMP Table: Employee's number, name, salary;

• Dept Table: Department name;

• Salgrade table: Salary grade;

2. Determine the known associated fields:

• Employees and departments: Emp.deptno=dept.deptno;

• Employees and wage levels: Emp.sal between Salgrade.losal and Salgrade.hisal;

Step One: find out the number, name, and salary of each employee

SELECT E.empno, E.ename, E.salfrom emp e;

Step Two: introduce the Department table and add a condition to eliminate the Cartesian product

SELECT E.empno, E.ename, E.sal, D.dnamefrom emp E, dept dwhere E.deptno=d.deptno;

The third step: The introduction of wage scale, continue to increase the elimination of Cartesian product conditions

SELECT E.empno, E.ename, E.sal, D.dname, S.gradefrom emp E, dept D, Salgrade Swhere E.deptno=d.deptno and e.sal between S. Losal and S.hisal;

If you now have the following further requirements: replace each salary level with a specific text message, for example:

1 replaced by a fifth salary, 2 replaced by a fourth salary, 3 replaced by a third salary, and so on, and by Decode () to achieve

SELECT E.empno, E.ename, E.sal, D.dnamedecode (s.grade,1, ' fifth wages ', 2, ' fourth wages ', 3, ' third wages ', 4, ' second-rate wages ', 5, ' first-rate wages ') Gradeinfofrom EMP E, dept D, Salgrade Swhere E.deptno=d.deptno and e.sal between S.losal and S.hisal;

All future topics are analyzed in a similar way, as long as the table is associated, there must be an associated field to eliminate the Cartesian product, except that the associated field needs to use a different qualifier according to the situation.

Second, left, right connection

The left and right connections refer to the reference direction of the query's judging criteria, for example, the following query:

SELECT * from EMP E, dept d WHERE E.deptno=d.deptno;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO DEPTNO dname LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------
7782 CLARK MANAGER 7839 September-June -81 2450 ACCOUNTING NEW YORK
7839 KING President 1 July-November -81 ACCOUNTING NEW YORK
7934 MILLER Clerk 7782 2 March-January -82 1300 ACCOUNTING NEW YORK
7369 SMITH Clerk 7902 1 July-December -80 the DALLAS
7876 ADAMS Clerk 7788 2 March-May -87 1100 DALLAS
7902 FORD ANALYST 7566 March-December -81 DALLAS
7788 SCOTT ANALYST 7566 1 September-April -87 DALLAS
7566 JONES MANAGER 7839 February-April -81 2975 DALLAS
7499 ALLEN salesman 7698 20月-February -81 + SALES CHICAGO
7698 BLAKE MANAGER 7839 January-May -81 2850 SALES CHICAGO
7654 MARTIN salesman 7698 2 August-September -81 1250 1400 SALES CHICAGO
7900 JAMES Clerk 7698 March-December -81 950 SALES CHICAGO
7844 TURNER salesman 7698 August-September -81 0 SALES CHICAGO
7521 WARD salesman 7698 2 February-February -81 1250 SALES CHICAGO

14 rows have been selected.

The department has a total of four, but now only returned three departments of information, the lack of 40 doors, because there is no record in the employee table is 40 department, so now will not display the 40 department of Information, that is: Now the query with the EMP table for reference, then if you want to display 40 door now? You have to change the direction of this reference, you need to use the left and right connections.

SELECT * from EMP E, dept D WHERE E.deptno (+) =d.deptno;

     EMPNO ename      job              mgr hiredate   &NB Sp          sal       COMM     DEPTNO     DEPTNO dname   &NB Sp      loc----------------------------------------------------------------------------------------- ---     7782 CLARK      manager         7839 September-June -81     &N Bsp     2450                    10         10  ACCOUNTING     NEW YORK      7839 KING       President            17-11 Month -81                           &N Bsp  10         ACCOUNTING     NEW YORK      7934 MILLER     CL ERK &nbsP         7782 2 March-January -82           1300           &NBS P        10         ACCOUNTING     NEW YORK      7369 SM ITH      clerk           7902 1 July-December -80            8                    20         ( )     DALLAS      7876 ADAMS      clerk           7788 23-5 Month -87           1100                    20 &nbs P        7902       DALLAS     The FORD       ANALYST &N Bsp       7566 March-December -81                       &NBS P     20         DALLAS,      ,      7788 SCOTT      analyst         7566 1 September-April -87               &NBS P              20         DALLAS       &NB Sp     7566 JONES      manager         7839 February-April -81           2975                    20         Resear  CH       DALLAS      7499 ALLEN      salesman        7698 20月-February -81            300                 &N Bsp     SALES          chicago      7698 BLAKE      manage R   &NBSp     7839 January-May -81           2850               &NBS P    30         SALES          chicago      7654 MA Rtin     salesman        7698 2 August-September -81           1250   &NBSP ;   1400         $          chicago SALES              7900 JAMES      clerk           7698 March-December -81     & nbsp      950                    30       &NBSP ; SALES          chicago      7844 TURNER     salesman     &N Bsp  7698 August-September -81           $          0       &NBS P         SALES          chicago      7521 WARD   &N Bsp   salesman        7698 2 February-February -81           1250       &NB sp;500          chicago         SALES         &NBSP ;                          ,         &NB Sp                          ,         &NB Sp                         OPERATIONS     Boston selected 1 5 lines.

Now that 40 doors have been found, the direction of the reference has changed, and the "(+)" is used to change the left and right connections, which have the following two usage conditions:

• (+) =: Placed on the left side of the equal sign, indicating a right connection;

• = (+): placed on the right side of the equal sign, indicating that the left join is connected;

But do not deliberately distinguish between the left or right, just based on the query results, if you find that some of the required data is not displayed, use this symbol to change the connection direction.

Example: querying each employee's name and leader's name

SELECT E.ename, E.job, M.enamefrom emp E, emp mwhere e.mgr=m.empno (+);

However, this symbol is unique to the Oracle database and cannot be used by other databases.

Third, sql:1999 grammar

In addition to the table join operation above, in SQL syntax, there is another set of operational SQL for table joins, in the following format:

SELECT table1.column,table2.columnfrom table1 [Cross JOIN table2]| [NATURAL JOIN table2]| [JOIN table2 USING (column_name)]| [JOIN table2 on (table1.column_name=table2.column_name)]| [left| Right| Full OUTER JOIN table2 on (table1.column_name=table2.column_name)];

The above is actually a union of multiple grammars, the following chunking illustrates the use of syntax.

1. Cross join: used to produce Cartesian product

SELECT * from the EMP Cross JOIN dept;

The Cartesian product itself is not a useless content, and in some cases it needs to be used.

2, Natural connection (NATURAL join): automatically find the matching related fields, eliminate the Cartesian product

SELECT * from EMP NATURAL JOIN dept;

However, not all fields are associated fields, and setting the associated fields needs to be specified by constraints;

3. JOIN ... Using clause: The user specifies an associated field that eliminates the Cartesian product

SELECT * from EMP JOIN dept USING (DEPTNO);

4. JOIN ... ON clause: The user specifies an association condition that can eliminate the Cartesian product

SELECT * from EMP JOIN dept on (EMP.DEPTNO=DEPT.DEPTNO);

5, the connection direction changes:

• Left (outer) connection: OUTER join ... On;

• Right (outside) connection: OUTER join ... On;

• Full (outside) connection: OUTER join ... On; --Displays data that is not in both tables

SELECT * from emp right OUTER JOIN dept on (EMP.DEPTNO=DEPT.DEPTNO);

Databases outside of Oracle use the sql:1999 syntax above, so this syntax has to be some (if you've been using Oracle all the time).

Again stressed: multi-table Query performance is certainly not high, and performance must be in the case of large data to be able to find.

Oracle Multi-table queries (1)