Before talking about multi-table queries, make a requirement that is not related to multi-table queries.
Suppose you have such a column of IDS in Table B
+------+
| id |
+------+
| 1 |
| 2 |
| 1 |
| 3 |
| 4 |
| 5 |
+------+Please indicate the serial number for the id column according to the query in table B and display it as follows:
+------+----------+
| id | sequence |
+------+----------+
| 1 | 0 |
| 2 | 2 |
| 1 | 0 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+------+----------+In fact, this is a simple sorting operation, that is, sorting columns. In Oracle, rownum is used to represent the order of columns.
As follows, create a table B1 and insert data.
SQL> create table B1(id number(1));
Table created.
SQL> begin
2 for i in 1..5
3 loop
4 insert into B1 values(i);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> insert into B1 values(2);
1 row created.
SQL> insert into B1 values(5);
1 row created.
SQL> select * from B1;
ID
----------
1
2
3
4
5
2
5
7 rows selected.
SQL> Realistic serial numbers through rownum
SQL> select rownum,id from B1;
ROWNUM ID
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 2
7 5
7 rows selected.
SQL> Of course, there's another way, which you can think about on your own.
**
Now start with an example of multi-table operations
**
I. union all
union all is usually used in a variety of situations to merge data sets
Example: There are two tables A and B with the following data:
SQL> select * from a1;
ID
----------
1
2
SQL> select * from c1;
ID
----------
3
4
1
The data set using union all is as follows:
**SQL> select * from a1
2 union all
3 select * from c1;
ID
----------
1
2
3
4
1
SQL> **It was found that union all would merge the data sets of the two tables. Note, however, that the data types of the two tables are identical. If not, the following ORA-01790 error will occur:
ORA-01790: expression must have same datatype as corresponding expression
2. Empty string
The empty string itself is a varchar2() data type, so it is different from Null. Null can be any data type.
UNION and OR
Usually, in queries, we use or to manipulate tables in terms. To fetch aggregates from multiple tables. Usually, we rewrite or to a union statement.
For example, we create a table emp2 and index on ename and empno columns.
SQL> create table emp2
2 as
3 select * from emp;
Table created.
SQL> create index idx_emp2_ename on emp2(ename);
Index created.
SQL> create index idx_emp2_empno on emp2(empno);
Index created.
SQL> Requirements, find empno 7788 and ename SCOTT names, salaries and employment dates.
-------------use firstorQuery as a filter condition---------------------
SQL> select ename,sal,hiredate
2 from emp2
3 where empno=7788 or ename=upper('scott');
ENAME SAL HIREDATE
---------- ---------- ---------
SCOTT 3000 19-APR-87
-----------Use belowunionQuery as join condition----------------------
SQL> select ename,sal,hiredate from emp2
2 where empno=7788
3 union
4 select ename,sal,hiredate from emp2
5 where ename=upper('scott');
ENAME SAL HIREDATE
---------- ---------- ---------
SCOTT 3000 19-APR-87
SQL> In the above example, we found that the query results are the same, but if union all is used here, the result will be wrong. As follows:
SQL> select ename,sal,hiredate from emp2
2 where empno=7788
3 union all
4 select ename,sal,hiredate from emp2
5 where ename=upper('scott');
ENAME SAL HIREDATE
---------- ---------- ---------
SCOTT 3000 19-APR-87
SCOTT 3000 19-APR-87
SQL> Note 1: The result is one more line, so using union will automatically remove duplicate lines.
Note 2: The query cost of using union and or will be different. We can view it by looking at the execution plan.
In order to eliminate the influence of bitmap convert er, the parameters are set first.
SQL> alter session set "_b_tree_bitmap_plans"=false;
Session altered.
First look at the execution plan when using or:
SQL> set timing on;
SQL> explain plan for
2 select ename,sal,hiredate from emp2
3 where empno=7788 or ename=upper('scott');
Explained.
Elapsed: 00:00:00.14
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2941272003
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP2 | 2 | 44 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7788 OR "ENAME"='SCOTT')
13 rows selected.
Elapsed: 00:00:00.27
SQL> We found that the full table scan was performed here, and we know that we have previously created an index on the ename,empno column, but we still go through the full table scan here.
When using union as a screening condition
SQL> explain plan for
2 select ename,sal,hiredate from emp2
3 where empno=7788
4 union
5 select ename,sal,hiredate from emp2
6 where ename=upper('scott');
Explained.
Elapsed: 00:00:00.10
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4161404436
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 6 (34)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 40 | 6 (34)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP2 | 1 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EMP2_EMPNO | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP2 | 1 | 18 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_EMP2_ENAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPNO"=7788)
6 - access("ENAME"='SCOTT')
19 rows selected.
Elapsed: 00:00:00.11
SQL> Here we find that when using Union, the index is used to retrieve data, and the query time is slightly less. There are only a dozen rows of data in this table, but if there are tens of thousands of data, the query time will be significantly slower.
Sometimes, however, union's de-duplication function may be incorrect.
The following data are listed:
Query the department number and number of employees whose mgr is 7698 and job is salesman:
SQL> select empno,deptno from emp2 where mgr=7698 order by 1;
EMPNO DEPTNO
---------- ----------
7499 30
7521 30
7654 30
7844 30
7900 30
Elapsed: 00:00:00.01
SQL> select empno,deptno from emp2 where job=upper('salesman') order by 1;
EMPNO DEPTNO
---------- ----------
7499 30
7521 30
7654 30
7844 30
Elapsed: 00:00:00.00
SQL> Five rows of data were found. So if you use or as a filter, it should be five rows of data.
SQL> select deptno from emp2
2 where mgr=7698 or job=upper('salesman') order by 1;
DEPTNO
----------
30
30
30
30
30
Elapsed: 00:00:00.00
SQL> But if union is used as the filter condition at this time, the following results will appear:
SQL> select deptno from emp2
2 where mgr=7698
3 union
4 select deptno from emp2
5 where job=upper('salesman')
6 order by 1;
DEPTNO
----------
30
Elapsed: 00:00:00.00
SQL> Well, we found that we had lost weight. There's only one line left.
The above examples show that:
1 > Not only will the results between two datasets be de-duplicated, but the results repeated by a single dataset will also be de-duplicated.
2 > Using union as a screening condition, the data may not be consistent with the expected results.
For the above mgr and job statements with union as the filter condition, you can simulate the following:
SQL> select distinct deptno
2 from
3 (
4 select deptno from emp2 where mgr=7698
5 union all
6 select deptno from emp2 where job=upper('salesman')
7 )
8 order by 1;
DEPTNO
----------
30
Elapsed: 00:00:00.11
SQL> Question: Can we use union to rewrite data sets that are not consistent with expected results like this? The answer is yes.
This effect of de-weighting can be eliminated by adding an empno unique column as follows:
SQL> select deptno
2 from
3 (
4 select empno,deptno from emp2 where mgr=7698
5 union
6 select empno,deptno from emp2 where job=upper('salesman')
7 )
8 order by 1;
DEPTNO
----------
30
30
30
30
30
Elapsed: 00:00:00.00
SQL> Of course, in addition to the unique column and the primary key, oracle also provides rownum, also known as pseudo columns, to represent columns.
SQL> select deptno
2 from
3 (
4 select rownum,deptno from emp2 where mgr=7698
5 union
6 select rownum,deptno from emp2 where job=upper('salesman')
7 )
8 order by 1;
DEPTNO
----------
30
30
30
30
30
Elapsed: 00:00:00.00
SQL> III. Combination of Relevance and Action
Compared with the data in the query form, we usually see more data returned in multiple tables.
Examples include: querying Department 10's employee number, name, department name and work address
SQL> select e.empno,e.ename,d.deptno,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno and e.deptno=10;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
Elapsed: 00:00:00.01
SQL> Internal connections can also be used as follows:
SQL> select e.empno,e.ename,d.deptno,d.dname
2 from emp e
3 inner join dept d on e.deptno=d.deptno
4 where e.deptno=10;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
Elapsed: 00:00:00.00
SQL> IV. IN, EXISTS and inner join
Example: First create a table emp3 with only four columns ename,job,sal,comm
SQL> create table emp3
2 as
3 select ename,job,sal,comm from emp where job='CLERK';
Table created.
Elapsed: 00:00:00.20
SQL> Requirements: Require the return of emp(empno,ename,job,sal,deptno) information that matches the data in the emp3 table (empno,job,sal, deptno)
1. Use IN
SQL> explain plan for
2 select empno,ename,job,sal,deptno
3 from emp
4 where (ename,job,sal) in
5 (select ename,job,sal from emp3);
Explained.
Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 349248158
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 40 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 350 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP3 | 4 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ENAME"="ENAME" AND "JOB"="JOB" AND "SAL"="SAL")
15 rows selected.
Elapsed: 00:00:00.02
SQL> 2. Writing of EXISTS
SQL> explain plan for
2 select empno,ename,job,sal,deptno
3 from emp a
4 where exists
5 (
6 select null from emp3 b
7 where a.ename=b.ename and a.job=b.job and a.sal=b.sal);
Explained.
Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 349248158
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 40 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 350 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP3 | 4 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ENAME"="B"."ENAME" AND "A"."JOB"="B"."JOB" AND
"A"."SAL"="B"."SAL")
16 rows selected.
Elapsed: 00:00:00.01
SQL> 3. Use inner join
SQL> explain plan for
2 select a.empno,a.ename,a.job,a.sal,a.deptno
3 from emp a
4 inner join emp3 b on
5 (a.ename=b.ename and a.job=b.job and a.sal=b.sal);
Explained.
Elapsed: 00:00:00.05
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 620718003
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 160 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 160 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP3 | 4 | 60 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 350 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ENAME"="B"."ENAME" AND "A"."JOB"="B"."JOB" AND
"A"."SAL"="B"."SAL")
16 rows selected.
Elapsed: 00:00:00.13
SQL> CONCLUSION: When using IN and EXISTS, the discoverer uses hash join semi (hash semi-join), and only hash join when using join, so the query efficiency of IN and EXISTS is the same.
**
5. Internal join, left join, right join, full join parsing
Start by creating two tables: using sql scripts here
SQL> ! cat c_join.sql
-----prompt--------
--this is left table
create table left as
select 'left_1' as str,'a' as v from dual
union all
select 'left_2','2' as v from dual
union all
select 'left_3','3' as v from dual
union all
select 'left_4','4' as v from dual;
-------end----------
-----prompt--------
--this is right table
create table right as
select 'right_3' as str,'3' as v,1 as status from dual
union all
select 'right_4' as str,'4' as v,0 as status from dual
union all
select 'right_5' as str,'5' as v,0 as status from dual
union all
select 'right_6' as str,'6' as v,0 as status from dual;
------end----------
SQL> Look at the data in two tables:
SQL> select * from left;
STR V
------ -
left_1 1
left_2 2
left_3 3
left_4 4
Elapsed: 00:00:00.01
SQL> select * from right;
STR V STATUS
------- - ----------
right_3 3 1
right_4 4 0
right_5 5 0
right_6 6 0
Elapsed: 00:00:00.00
SQL> **
1,inner join
Returns the matching data set in the two tables.
SQL> select * from left;
STR V
------ -
left_1 1
left_2 2
left_3 3
left_4 4
Elapsed: 00:00:00.01
SQL> select * from right;
STR V STATUS
------- - ----------
right_3 3 1
right_4 4 0
right_5 5 0
right_6 6 0
Elapsed: 00:00:00.00
SQL>
SQL> select l.str as left_str,r.str as right_str
2 from left l
3 inner join right r on
4 l.v=r.v;
LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
Elapsed: 00:00:00.00
SQL> 2,left join
Returns all data sets in the left table, while the right table only returns data sets matching the left table.
SQL> select * from left;
STR V
------ -
left_1 1
left_2 2
left_3 3
left_4 4
Elapsed: 00:00:00.01
SQL> select * from right;
STR V STATUS
------- - ----------
right_3 3 1
right_4 4 0
right_5 5 0
right_6 6 0
Elapsed: 00:00:00.00
SQL> select l.str as left_str,r.str as right_str
2 from left l
3 left join right r on
4 l.v=r.v;
LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
left_2
left_1
Elapsed: 00:00:00.00
SQL>
The above is the quasi-writing of SQL 99, but ORACLE extends it to
**
+
** Differentiation
It can also be rewritten as
SQL> select l.str as left_str,r.str as right_str
2 from left l,right r
3 where l.v=r.v(+)
4 ;
LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
left_2
left_1
Elapsed: 00:00:00.00
SQL> 3,right join
Contrary to left join
SQL> select l.str as left_str,r.str as right_str
2 from left l
3 right join right r on
4 l.v=r.v
5 order by 1,2;
LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
right_5
right_6
Elapsed: 00:00:00.00
SQL> The rewritten version is as follows:
SQL> select l.str as left_str,r.str as right_str
2 from left l,right r
3 where l.v(+)=r.v
4 order by 1,2;
LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
right_5
right_6
Elapsed: 00:00:00.00
SQL> 4,full join
Returns all data sets from two tables, but the matched data sets are displayed on the same row, while the unmatched data sets only show one table.
SQL> select l.str as left_str,r.str as right_str
2 from left l
3 full join right r
4 on l.v=r.v
5 order by 1,2;
LEFT_S RIGHT_S
left_1
left_2
left_3 right_3
left_4 right_4
right_5
right_6
6 rows selected.
Elapsed: 00:00:00.00
SQL>
"`