Oracle Database Seventh Multi-table Query

Time:2019-10-13

7. Multi-table query

For queries, we have learned simple queries, limited queries and query ranking before, which belong to the standard statements of SQL. The main function of the single-line function in the previous chapter is to make up for the deficiencies of queries.

From the beginning of multi-table query, it has formally entered the complex query part.

7.1. Basic Grammar

  • A multi-table query is a query statement that extracts the required data from multiple tables together. If you want to query multiple tables, just follow the FROM clause with multiple tables. The grammar is as follows:

    SELECT [DISTINCT]* | Column name [AS] [Column alias], Column name [AS] [Column alias],...
    From table name 1 [table alias 1], table name 2 [table alias 2],...
    [WHERE condition (s)]
    [ORDER BY sorted field 1 ASC | DESC, sorted field 2 ASC | DESC,...];
  • Next, we will use EMP table and Dept table to query multiple tables together. Before querying, we need to do one more step. First, we can determine the amount of data in EMP and Dept tables, and we can use COUNT () function to count them.

ExampleStatistical EMP table data volume (14 rows)

SELECT COUNT(*) 
FROM emp;

ExampleStatistical data in dept table (4 rows)

SELECT COUNT(*) 
FROM dept;

ExampleNow check all the details of all employees and departments

SELECT COUNT(*) 
FROM emp,dept;
  • It was found that a total of 56 rows of records were returned, which isCartesian productThe problems caused. Now it is found that the appearance of Cartesian product can make the query result very huge. If the data volume of both tables is very large, then this huge is terrible, so now we must find a way to eliminate the Cartesian product.
  • Generally speaking, if we want to eliminate Cartesian product, we often use related fields. Because there may be duplicate fields between multiple tables, we need to add table names before accessing duplicate names, and use the way of “table name fields” to access them.

ExampleUsing equivalence condition to deal with Cartesian product

SELECT * 
FROM emp,dept
WHERE emp.deptno = dept.deptno;
  • The result shows that the Cartesian product has been eliminated, but the product still exists, just not shown.
  • Now that the basic concepts are clear, we can do an analysis of the amount of data. There is a sh user in Oracle, of course, which is saved in the pdbmldn plug-in database
    • 1. Open sqlplus: Run-Enter sqlplus/nolog
    • 2. Administrators connect to the database: conn sys/change_on_install AS SYSDBA;
    • 3. Switch to pdbmldn database: ALTER SESSION SET CONTAINER = pdbmldn;
    • 4. Open pdbmldn database: ALTER DATABASE pdbmldn OPEN
    • You need to use the SH user to operate immediately. Now you can use the sales and costs tables under the SH user. The number of these two tables is relatively large.

ExampleLook at the amount of data in the sh. sales table (918843 records)

SELECT COUNT(*)
FROM sh.sales;

ExampleLook at the amount of data in the sh. costs table (82112 records)

SELECT COUNT(*)
FROM sh.costs;

ExampleIf you query these two tables directly now, look at the problem

SELECT COUNT(*)
FROM sh.sales,sh.costs;
  • Once execution is started, the waiting process will be long, but then all data quantities (including Cartesian product) are displayed, and then elimination is started, using equivalence correlation. The amount of data that will eventually be returned: 75,448,036,416
  • Although all the Cartesian products displayed are eliminated, the principle and mechanism of database and the representation of Cartesian products will always exist.
  • Multi-table query can produce Cartesian product, so its performance is poor. It can eliminate Cartesian product by using equivalent correlation fields.

7.2. Examples of multi-table queries

  • Although multi-table query has its own performance problems, it does not mean that multi-table query can not be used, and some more reasonable methods are needed to solve the problem of multi-table query.

ExampleQuery the number, name, position, basic salary, department name and location information of each employee

  • Determine the required data tables
    • EMP table: query the number, name, position and basic salary of each employee;
    • Dept table: Department name, Department location.
  • Determine known associated fields
    • Departments and employees: emp. deptno = Dept. deptno
  • Then you need to follow the execution steps of an SQL statement: FROM, WHERE, SELECT.
SELECT emp.empno,emp.ename ,emp.job,emp.sal,dept.dname,dept.loc
FROM emp,dept
WHERE emp.deptno = dept.deptno
  • But there is a problem here. The above programs all use column names accessed by table names, if the table names are very long now: yuzhou_yinhexi_diqiu_yazhou_zhongguo_beijing. So it is often used to define aliases for query data in multi-table queries, and the aliases are also defined in the FROM clause. The above program can be rewritten as follows:
SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno=d.deptno;
  • Aliases will almost always be used in future programming

ExampleFind out the number, name, date of employment, basic salary and salary level of each employee.

  • Determine the required data tables
    • Emp table: inquire the number, name, date of employment and basic salary of each employee;
    • Salgrade table: Wage scale.
  • Determine known associated fields
    • Employees and salary levels: emp. sal BETWEEN salgrade. local and salgrade. hisal;
SELECT e.empno, e.ename, e.hiredate, e.sal, s.grade
FROM emp e, salgrade s 
WHERE e.sal BETWEEN s.losal AND s.hisal;
  • In the previous query, we found that only the number 1, 2, 3, 4, 5 was displayed. Now we hope to replace it with Chinese. If we want to replace it, we must use the DECODE () function.

ExampleIn order to display the information of wage grade more clearly, it is now hoped that it can be replaced by the following format:

Grad = 1: shown as “Equivalent Wages”

Grad = 2: shown as “D Wage”

Grad = 3: shown as “C Wage”

Grad = 4: shown as “B Wage”

Grad = 5: shown as “A Wage”

SELECT e.empno, e.ename, e.hiredate, e.sal,
    DECODE (s. grade, 1,'E and other wages', 2,'D and other wages', 3,'C and other wages', 4,'B and other wages', 5,'A and other wages')grade
FROM emp e, salgrade s 
WHERE e.sal BETWEEN s.losal AND s.hisal;
  • What I have mentioned before is multi-table query for two tables, and only one condition is used to eliminate Cartesian product in multi-table query. If there are many conditions to eliminate Cartesian product now, then we often use AND to link these conditions together.

ExampleFind out the name, position, basic salary, department name and salary level of each employee.

  • Determine the required data tables
    • Emp table: the name, position and basic salary of each employee;
    • Dept table: department name
    • Salgrade table: Wage scale.
  • Determine known associated fields
    • Employees and Departments: emp. deptno = Dept. deptno
    • Employees and salary levels: emp. sal BETWEEN salgrade. local and salgrade. hisal;
SELECT e.ename, e.job, e.sal, d.dname,
    DECODE (s. grade, 1,'E and other wages', 2,'D and other wages', 3,'C and other wages', 4,'B and other wages', 5,'A and other wages')grade
FROM emp e, dept d, salgrade s 
WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
  • In multi-table queries, every time an associated table is added, the conditions for eliminating Cartesian product need to be set.

7.3. Connection operation of tables

  • There are two kinds of join operations for data tables defined in the database:
    • Internal join: Also known as equivalent join (or join, also known as ordinary join or natural join), is the earliest form of join. Internal join deletes all tuples from the result table that do not match rows from other joined tables, so internal join may lose information when matching conditions are not satisfied. The connection methods used before belong to inner connection, and the condition of eliminating Cartesian product set in WHERE clause is carried out by means of equivalence judgment.
    • External Connection: In the internal connection, only the equivalent conditions can be displayed, but if the conditions are not satisfied, it can not be displayed. If you want the data in a particular table to be displayed in full, you can use the external connection, which can be divided into three types: left external connection (abbreviated as left connection), right external connection (abbreviated as right connection), total external connection (abbreviated as full connection, in the SQL:1999 grammar section). Explain separately)
  • Table join operations previously written belong to the definition of inner join
  • In order to better observe the differences in connection modes, we first need to add a data to the EMP table, which will be explained later.
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) 
    VALUES (8888,'Li Huahua','CLERK', 7369, SYSDATE, 800, 100, null);
  • At this point, the added data is an employee without a department, that is, the employee’s department number is null. When the added data is complete, check all the current data in the EMP table.
SELECT * FROM emp;
  • There is no department number here, so let’s show the effect of equivalent connection.

ExampleUsing Equivalent Connection

SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno;
  • Two problems can be found through the results at this time:
    • Question 1: employees without departments do not show
    • Question 2: One 40 departments are not shown.
  • So now you can see that only data that meets the connection criteria will be fully displayed in the use of internal connections. But if you want the data in EMP or dept tables to be fully displayed now, you can use external links.
  • External connections are now mainly used in two ways:
    • Left External Connection: Left Relational Attribute = Right Relational Attribute (+), +placed on the right side of the equal sign, indicating the left connection;
    • Right External Connection: Left Relational Attribute (+) = Right Relational Attribute, +placed on the left side of the equal sign, indicating the right connection.

ExampleUse the left outer connection to display the employee number 8888

SELECT *
FROM emp e, dept d 
WHERE e.deptno = d.deptno(+);

ExampleUse the right outer connection to display the information with the door number 40

SELECT *
FROM emp e, dept d 
WHERE e.deptno(+) = d.deptno;
  • In the above results, because 40 departments have no employees, all employee information is null.
  • Personal summary: in the environment of using external connection, if the required data information is not displayed, then use external connection. Specifically, left or right, I don’t think it is necessary to remember, just try!

7.4. Self-correlation

  • There is a Mgr field in the EMP table that represents the employee’s leadership
SELECT * FROM emp;
  • Now, if you want to display the employee’s leadership information, you must use the employee table and the employee table’s own connection operation to complete. Use the leader number in the employee table to find the employee information corresponding to this number.

ExampleFind out the number and name of each employee and the number and name of their superiors.

  • Determine the required data tables
    • Emp table: Employee’s number, name
    • Emp table: find the number and name of the leader
  • Determine known associated fields
    • Employees and Leaders: emp. Mgr = memp. empno (Employee’s Leadership Number = Leadership Information)
  • Step 1: Direct operation of self-connection
SELECT e.empno eno, e.ename ename, m.empno mno, m.ename mname
FROM emp e, emp m 
WHERE e.mgr = m.empno;

There are now 15 records in the table, but only 14 records show that it is impossible to display data when the equivalent connection is unconditionally satisfied.

In EMP tables, king is an employee who has no leadership. External connections must be considered at this time.

  • Step 2: Use the left outer connection
SELECT e.empno eno, e.ename ename, m.empno mno, m.ename mname
FROM emp e, emp m 
WHERE e.mgr = m.empno(+);

For employees without leadership information, null is used to represent the corresponding leadership information.

ExampleFind out the number, name, date of employment (shown by year-month-day), job, leader’s name, employee’s monthly salary, employee’s annual salary (basic salary + bonus), employee’s salary level, Department number, Department name, Department location, and require the basic monthly salary of these employees to be between 1500 and 3500, and then the final result will be based on the year. Wages are arranged in descending order. If annual wages are equal, they are arranged by work.

  • Determine the required data tables
    • Emp table: number, name, date of employment, work, monthly wage, annual wage;
    • EMP table: leader’s name;
    • Dept table: Department number, name and location;
    • Salgrade table: Wage scale.
  • Determine known associated fields
    • Employees and leaders: emp. Mgr = memp. empno;
    • Employees and Departments: emp. deptno = Dept. deptno;
    • Employees and salary levels: emp.sal BETWEEN salgrade.losal and salgrade.hisal.
  • Step 1: Find out the number, name, date of employment, job, monthly salary, annual salary, and monthly salary of all employees employed in 1981 between 1500 and 3500. Only EMP single table is needed.
SELECT e.empno, e.ename, e.hiredate, e.sal, (e.sal+NVL(e.comm,0))*12 income
FROM emp e 
WHERE TO_CHAR(e.hiredate,'yyyy') = '1981' AND e.sal BETWEEN 1500 AND 3500;
  • Step 2: Add leadership information and use self-association
SELECT e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+NVL(e.comm,0))*12 income,
    m.ename mname
FROM emp e, emp m 
WHERE TO_CHAR(e.hiredate,'yyyy') = '1981' AND e.sal BETWEEN 1500 AND 3500
    AND e.mgr = m.empno(+);
  • Step 3: Join Departmental Information
SELECT e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+NVL(e.comm,0))*12 income,
    m.ename mname, d.deptno dno, d.dname dname, d.loc 
FROM emp e, emp m, dept d  
WHERE TO_CHAR(e.hiredate,'yyyy') = '1981' AND e.sal BETWEEN 1500 AND 3500
    AND e.mgr = m.empno(+)
    AND e.deptno = d.deptno;
  • Step 4: Find out the salary scale
SELECT e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+NVL(e.comm,0))*12 income,
    m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade
FROM emp e, emp m, dept d,salgrade s   
WHERE TO_CHAR(e.hiredate,'yyyy') = '1981' AND e.sal BETWEEN 1500 AND 3500
    AND e.mgr = m.empno(+)
    AND e.deptno = d.deptno
    AND e.sal BETWEEN s.losal AND s.hisal;
SELECT e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+NVL(e.comm,0))*12 income,
    m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade,
    DECODE (s. grade, 1,'E wage', 2,'D wage', 3,'C wage', 4,'B wage', 5,'A wage') wage level
FROM emp e, emp m, dept d,salgrade s   
WHERE TO_CHAR(e.hiredate,'yyyy') = '1981' AND e.sal BETWEEN 1500 AND 3500
    AND e.mgr = m.empno(+)
    AND e.deptno = d.deptno
    AND e.sal BETWEEN s.losal AND s.hisal;
  • Step 5: Sort. Since SELECT is executed before the ORDER BY clause, the ORDER BY clause can be used directly as an alias defined in the SELECT clause.
SELECT e.empno, e.ename, e.hiredate, e.job, e.sal, (e.sal+NVL(e.comm,0))*12 income,
    m.ename mname, d.deptno dno, d.dname dname, d.loc, s.grade,
    DECODE (s. grade, 1,'E wage', 2,'D wage', 3,'C wage', 4,'B wage', 5,'A wage') wage level
FROM emp e, emp m, dept d,salgrade s   
WHERE TO_CHAR(e.hiredate,'yyyy') = '1981' AND e.sal BETWEEN 1500 AND 3500
    AND e.mgr = m.empno(+)
    AND e.deptno = d.deptno
    AND e.sal BETWEEN s.losal AND s.hisal;
ORDER BY income DESC, e.job;
  • Through this slightly more complex topic, we can find that all the analysis must be carried out step by step, and these analysis processes need a lot of practice to consolidate.
  • Self-association belongs to the case where a table is self-related, and Cartesian product still occurs at this time.

7.5. SQL: 1999 Syntax Support

  • The “(+)” tag previously used is only suitable for applications in Oracle databases, but it cannot be used in other databases.
  • SQL: 1999 Grammar
SELECT [DISTINCT]* | Column name [AS] [Column alias], Column name [AS] [Column alias],...
FROM Table 1 Alias 1 [CROSS JOIN Table 2 Alias 2]|
[NATURAL JOIN Table 2 Table Alias 2]|
[JOIN Table 2 USING (Associated Column Name)]|
[JOIN Table 2 ON (Association Conditions)]|
[LEFT | RIGHT | FULL OUTER JOIN Table 2 ON (Association Conditions)]
[WHERE condition (s)]
[ORDER BY sorted field 1 ASC | DESC, sorted field 2 ASC | DESC,...];
  • In fact, what I have given above is a comprehensive grammar, which will be explained in detail below.

7.5.1 Cross Connection

  • CROSS JOIN acts on two relationships, and each tuple of the first relationship is connected to all tuples of the second relationship. This operation is exactly the same as Cartesian product. The grammar of cross-connection is as follows:
SELECT [DISTINCT]* | Column name [AS] [Column alias], Column name [AS] [Column alias],...
FROM Table 1 Alias 1 [CROSS JOIN Table 2 Alias 2]|
[WHERE condition (s)]
[ORDER BY sorted field 1 ASC | DESC, sorted field 2 ASC | DESC,...];
  • The main function of cross-connection is to produce Cartesian product.

ExampleUse cross-linking to query information

SELECT * 
FROM emp CROSS JOIN dept;
  • Generally speaking, there must be associated fields to eliminate Cartesian product when making multi-table joins, and the names of associated fields will generally be the same, if not the same, there will be some identical, now we are discussing the same situation, such as deptno field, which means the same, we can use natural joins to eliminate Cartesian product.

7.5.2 Natural Connection

  • Natural join (NATURAL JOIN) operations act on two relationships and eventually produce a relationship as a result. Unlike cross join (Cartesian product), natural join only considers tuple pairs with the same values on attributes that occur in both relational patterns. The operation grammar of natural connection is as follows:
SELECT [DISTINCT]* | Column name [AS] [Column alias], Column name [AS] [Column alias],...
FROM Table 1 Alias 1 [NATURAL JOIN Table 2 Alias 2]|
[WHERE condition (s)]
[ORDER BY sorted field 1 ASC | DESC, sorted field 2 ASC | DESC,...];

ExampleUse natural join to query information

SELECT * 
FROM emp NATURAL JOIN dept;
  • At this point, the connected fields will be displayed on the first column, and this is a way of internal connection.

7.5.3 USING clause

  • The Cartesian product can be eliminated directly by natural connection. If there is no such correlation field in the two tables, the Cartesian product can be eliminated by USING clause. The grammar of USING clause is as follows:
SELECT [DISTINCT]* | Column name [AS] [Column alias], Column name [AS] [Column alias],...
FROM Table 1 Table Alias 1 [JOIN Table 2 USING (Associated Column Name)]|
[WHERE condition (s)]
[ORDER BY sorted field 1 ASC | DESC, sorted field 2 ASC | DESC,...];

ExampleUse the USING clause to query information

SELECT * 
FROM emp JOIN dept USING(deptno);

7.5.4 ON clause

  • In the previous compilation of equivalent join, the Relevant Field is used to eliminate Cartesian product. Then the user can set an association condition manually by using ON clause in SQL:1999 grammar. The ON clause grammar is as follows:
SELECT [DISTINCT]* | Column name [AS] [Column alias], Column name [AS] [Column alias],...
FROM Table 1 Table Alias 1 [JOIN Table 2 ON (Association Conditions)]|
[WHERE condition (s)]
[ORDER BY sorted field 1 ASC | DESC, sorted field 2 ASC | DESC,...];
  • USING is to set the connection field, and ON is to set the connection condition.

ExampleUse the ON clause to query information

SELECT * 
FROM emp e JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal);

7.5.5 External Connection

  • In data query operation, data external connection can be divided into three forms: left external connection, right external connection and total external connection. At this time, the grammar of connection is as follows:
SELECT [DISTINCT]* | Column name [AS] [Column alias], Column name [AS] [Column alias],...
FROM Table 1 Table Alias 1 [LEFT | RIGHT | FULL OUTER JOIN Table 2 ON (Association Conditions)]
[WHERE condition (s)]
[ORDER BY sorted field 1 ASC | DESC, sorted field 2 ASC | DESC,...];
  • For external connections, the previous use is “(+)”, which can only achieve left or right external connections, but for total external connections can not be used, and total external connections can only rely on the content specified in the SQL: 1999 grammar.

ExampleImplementing Right Outside Connection

SELECT * 
FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno);

ExampleImplementing Left Outer Connection

SELECT * 
FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno);

ExampleTo achieve full external connection

SELECT * 
FROM emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno);
  • Through the above analysis, we can find that only the full-out connection can be realized through the SQL: 1999 grammar in all the grammars given, but the use of this kind of full-out connection is not very much.
  • And I suggest that if you use Oracle database, you use the “(+)” tag to control left and right connections, and not use it to achieve internal connections.

7.6. Set operation of data

  • Collective operation of data refers to the operation of query results.
  • Set operation is a binary operator, which includes four operators: union, difference, intersection and Cartesian product. Among them, Cartesian product has been demonstrated before, so this time we mainly look at three operations: union, intersection and difference. The grammar of the operation set is as follows
Query statement
    [UNION | UNION ALL | INTERSECT | MINUS]
Query statement
    ...
  • To realize the operation of a set, there are four operators:
    • UNION (union): Returns the entire contents of several query results, but duplicate tuples are not displayed;
    • UNION ALL (union): Returns the entire contents of several query results, and duplicate tuples are displayed.
    • MINUS (difference set): Returns different parts of several query results
    • INTERSECT: Returns the same part of several query results

7.6.1 union operation

  • The union operation is to connect the results of multiple queries together. For the union operation, two operators are provided: UNION (no duplication) and UNION ALL (duplication).

ExampleUnion operation: UNION, UNION ALL

  • First query
SELECT * FROM dept;
  • Second query
SELECT * FROM dept WHERE deptno = 10;

At this time, the structure of the columns returned by the two query results is the same.

  • Using UNION
SELECT * FROM dept
    UNION
SELECT * FROM dept WHERE deptno = 10;

The first query already contains the content of the second query, so the duplicate data is not displayed.

Example: use union all to display all

SELECT * FROM dept
    UNION ALL
SELECT * FROM dept WHERE deptno = 10;
  • Tips:It is suggested that UNION or UNION ALL be used to replace OR as much as possible in the future query operation writing process.

ExampleQuery all salespeople and clerks

  • Achievement 1:
SELECT * FROM emp WHERE job = 'SALESMAN' OR job = 'CLERK';
  • Achieving two:
SELECT * FROM emp WHERE job IN ('SALESMAN', 'CLERK');
  • Achieving three:
SELECT * FROM emp WHERE job = 'SALESMAN'
    UNION
SELECT * FROM emp WHERE job = 'CLERK'

7.6.2 differential set operation

ExampleUsing MINUS to perform difference set operations

SELECT * FROM dept
    MINUS
SELECT * FROM dept WHERE deptno = 10;
  • The result shows three lines, deptno = 10 is not shown.

7.6.3 Intersection Operation

ExampleUse INTERSECT to perform intersection operations

SELECT * FROM dept
    INTERSECT
SELECT * FROM dept WHERE deptno = 10;
  • The result shows only deptno = 10 lines.
  • When collecting operations, each query statement returns the same structure.

Description: This learning material is based on Li Xinghua’s Oracle development of combat classics.