Oracle PL/SQL/Result Set/UNION
Содержание
- 1 A UNION query that uses an ORDER BY clause
- 2 Combining Data with UNION and subquery
- 3 Combining Data with UNION for single column
- 4 Eliminate the duplication and mimic a UNION operator by appending the DISTINCT operator
- 5 Take the result of the MULTISET UNION DISTINCT operation and pass it as an argument to the SET operator to eliminate duplicates.
- 6 UNION: adds all rows from the first and the second query, eliminate any duplicates, and return the results
- 7 Union date column
- 8 Union dba_indexes and dba_tables
- 9 Union DBA_TABLES and DBA_INDEXES
- 10 Union result set in action
- 11 Union two complex queries and subquery
- 12 union with like operator
- 13 Using union in subquery
A UNION query that uses an ORDER BY clause
<source lang="sql">
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SQL> SQL> -- A UNION query that uses an ORDER BY clause: SQL> SQL> select empno, ename
2 from emp
3 where sal > 2000
4 UNION ALL
5 select empno, ename
6 from emp
7 where sal > 1000
8 ORDER BY 2;
EMPNO ENAME
----------
7876 ADAMS
7499 ALLEN
7698 BLAKE
7698 BLAKE
7782 CLARK
7782 CLARK
7902 FORD
7902 FORD
7566 JONES
7566 JONES
7839 KING
EMPNO ENAME
----------
7839 KING
7654 MARTIN
7934 MILLER
7788 SCOTT
7788 SCOTT
7844 TURNER
7521 WARD
SQL> SQL> SQL> drop table emp; SQL> SQL> SQL>
</source>
Combining Data with UNION and subquery
<source lang="sql">
SQL> SQL> CREATE TABLE Instructor (
2 InstructorID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL);
Table created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (1,"Victor"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (2,"Bill"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (3,"Mary"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (4,"Jack"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (5,"Peter"); 1 row created. SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (6,"Tom"); 1 row created. SQL> SQL> SQL> CREATE TABLE Course (
2 CourseID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50), 4 Credits INT);
Table created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (1,"SQL",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (2,"Java",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (3,"XML",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (4,"ERP",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (5,"Oracle",5); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY, 3 Comments VARCHAR(50), 4 Capacity INT);
SQL> SQL> SQL> SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY, 3 CourseID INT NOT NULL, 4 InstructorID INT NOT NULL, 5 RoomID INT NOT NULL, 6 Time VARCHAR(50));
Table created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (5,5,2,9,"Tue 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (6,6,3,2,"Tue 16:00-17:00, Thu 15:00-17:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (7,7,4,3,"Wed 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (8,8,5,8,"Wed 11:00-13:00, Fri 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (9,9,1,5,"Fri 11:00-13:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (10,10,6,9,"Fri 14:00-16:00"); 1 row created. SQL> SQL> SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL);
Table created. SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat"); 1 row created. SQL> SQL> CREATE TABLE Exam (
2 ExamID INT NOT NULL PRIMARY KEY, 3 CourseID INT NOT NULL, 4 InstructorID INT NOT NULL, 5 SustainedOn DATE, 6 Comments VARCHAR(255));
Table created. SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn,Comments) VALUES (1,1,1,DATE "2003-03-12","Hard"); 1 row created. SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn,Comments) VALUES (2,2,1,DATE "2003-03-13","Simple"); 1 row created. SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn,Comments) VALUES (3,3,2,DATE "2003-03-11","1 hour long"); 1 row created. SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn) VALUES (4,4,3,DATE "2003-03-18"); 1 row created. SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn,Comments) VALUES (5,5,2,DATE "2003-03-19","2 hours long"); 1 row created. SQL> SQL> CREATE TABLE dept (
2 deptID INT NOT NULL PRIMARY KEY, 3 empID INT NOT NULL, 4 ClassID INT NOT NULL, 5 EnrolledOn DATE, 6 Grade INT);
Table created. SQL> SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE "2002-09-23",62); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE "2002-09-30",70); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE "2003-09-23",51); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE "2003-09-23",41); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE "2003-09-23",68); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE "2002-09-30",78); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE "2002-09-30",80); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (8,4,8,DATE "2002-09-20",70); 1 row created. SQL> SQL> CREATE TABLE empExam (
2 empID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 Taken SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));
Table created. SQL> SQL> SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SQL> SELECT Name, "Instructor" As Role FROM Instructor
2 WHERE InstructorID = ( 3 SELECT InstructorID FROM Class WHERE ClassID = 1) 4 UNION 5 SELECT Name, "emp" FROM emp 6 WHERE empID IN ( 7 SELECT empID FROM dept WHERE ClassID = 1);
NAME
ROLE
Tom emp Victor Instructor
2 rows selected. SQL> SQL> SQL> drop table Instructor; Table dropped. SQL> drop table Class; Table dropped. SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.
</source>
Combining Data with UNION for single column
<source lang="sql">
SQL> SQL> CREATE TABLE empExam (
2 empID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 Taken SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));
SQL> SQL> SQL> SQL> CREATE TABLE dept (
2 deptID INT NOT NULL PRIMARY KEY, 3 empID INT NOT NULL, 4 ClassID INT NOT NULL, 5 EnrolledOn DATE, 6 Grade INT);
Table created. SQL> SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE "2002-09-23",62); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE "2002-09-30",70); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE "2003-09-23",51); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE "2003-09-23",41); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE "2003-09-23",68); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE "2002-09-30",78); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE "2002-09-30",80); 1 row created. SQL> SQL> SQL> SELECT empID
2 FROM empExam
3 WHERE Mark < 40
4 UNION
5 SELECT empID
6 FROM dept
7 WHERE GRADE < 40
8 ORDER BY empID;
EMPID
2
1 row selected. SQL> SQL> SQL> drop table empExam; Table dropped. SQL> drop table dept; Table dropped.
</source>
Eliminate the duplication and mimic a UNION operator by appending the DISTINCT operator
<source lang="sql">
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created. SQL> SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000); 3 BEGIN 4 FOR i IN set_in.FIRST..set_in.LAST LOOP 5 returnValue := set_in(i)||" "; 6 END LOOP; 7 RETURN returnValue; 8 END format_list; 9 /
Function created. SQL> SQL> DECLARE
2 a LIST := list(1,2,3,4); 3 b LIST := list(4,5,6,7); 4 BEGIN 5 dbms_output.put_line(format_list(a MULTISET UNION DISTINCT b)); 6 END; 7 /
7 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Take the result of the MULTISET UNION DISTINCT operation and pass it as an argument to the SET operator to eliminate duplicates.
<source lang="sql">
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created. SQL> SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000); 3 BEGIN 4 FOR i IN set_in.FIRST..set_in.LAST LOOP 5 returnValue := set_in(i)||" "; 6 END LOOP; 7 RETURN returnValue; 8 END format_list; 9 /
Function created. SQL> SQL> DECLARE
2 a LIST := list(1,2,3,4); 3 b LIST := list(4,5,6,7); 4 BEGIN 5 dbms_output.put_line(format_list(SET(a MULTISET UNION b))); 6 END; 7 /
7 PL/SQL procedure successfully completed. SQL>
</source>
UNION: adds all rows from the first and the second query, eliminate any duplicates, and return the results
<source lang="sql">
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected. SQL> SQL> -- A UNION statement adds all those rows from the first query to all the rows in the second query, eliminate any duplicates, and return the results. SQL> SQL> SQL> select empno, ename
2 from emp
3 where ename like "A%"
4 or ename like "B%"
5 union
6 select empno, ename
7 from emp
8 where ename like "B%"
9 or ename like "C%";
EMPNO ENAME
----------
7499 ALLEN
7698 BLAKE
7782 CLARK
7876 ADAMS
SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL> SQL> SQL>
</source>
Union date column
<source lang="sql">
SQL> CREATE TABLE emp (
2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 );
Table created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SELECT hire_date emp_date
2 FROM emp 3 UNION 4 SELECT end_date 5 FROM emp;
15-NOV-61 16-SEP-64 23-AUG-76 29-DEC-87 01-MAR-94 02-JAN-04 03-MAR-04 01-APR-04 04-APR-04 05-MAY-04 15-JUN-04 30-SEP-04 31-OCT-04 15-NOV-04
15 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Union dba_indexes and dba_tables
<source lang="sql">
SQL> select
2 Owner,
3 Tablespace_Name,
4 COUNT(*)||" tables" Objects
5 from DBA_TABLES
6 group by
7 Owner,
8 Tablespace_Name
9 union
10 select
11 Owner,
12 Tablespace_Name,
13 COUNT(*)||" indexes" Objects
14 from dba_indexes
15 group by
16 Owner,
17 Tablespace_Name;
OWNER TABLESPACE_NAME OBJECTS
------------------------------ ------------------------------------------------
CTXSYS SYSAUX 26 tables CTXSYS SYSAUX 47 indexes CTXSYS 11 tables DBSNMP SYSAUX 17 tables DBSNMP SYSAUX 8 indexes DBSNMP 2 indexes DBSNMP 4 tables DEFINER SYSTEM 1 indexes DEFINER 1 tables FLOWS_020100 SYSAUX 160 tables FLOWS_020100 SYSAUX 422 indexes FLOWS_020100 4 tables FLOWS_FILES SYSAUX 1 tables FLOWS_FILES SYSAUX 5 indexes HR USERS 19 indexes HR USERS 6 tables HR 1 tables INV10 SYSTEM 1 indexes INV10 1 tables INV11 SYSTEM 1 indexes INV11 1 tables INV12 SYSTEM 1 indexes INV12 1 tables INV13 SYSTEM 1 indexes INV13 1 tables INV14 SYSTEM 1 indexes INV14 1 tables INV15 SYSTEM 1 indexes INV15 1 tables INV16 SYSTEM 1 indexes INV16 1 tables INV17 SYSTEM 1 indexes INV17 1 tables INV18 SYSTEM 1 indexes INV18 1 tables INV19 SYSTEM 1 indexes INV19 1 tables INV20 SYSTEM 1 indexes INV20 1 tables sqle SYSTEM 12 indexes sqle SYSTEM 14 tables sqle 3 indexes sqle 9 tables MDSYS SYSTEM 30 tables MDSYS SYSTEM 51 indexes MDSYS 3 indexes MDSYS 7 tables OUTLN SYSTEM 3 tables OUTLN SYSTEM 4 indexes SYS SYSAUX 175 tables SYS SYSAUX 247 indexes SYS SYSTEM 402 tables SYS SYSTEM 513 indexes SYS 28 indexes SYS 92 tables SYSTEM SYSAUX 21 indexes SYSTEM SYSAUX 22 tables SYSTEM SYSTEM 145 indexes SYSTEM SYSTEM 85 tables SYSTEM 31 tables SYSTEM 36 indexes TSMSYS SYSAUX 1 tables TSMSYS SYSAUX 2 indexes XDB SYSAUX 11 tables XDB SYSAUX 384 indexes XDB 1 indexes
SQL> SQL> SQL>
</source>
Union DBA_TABLES and DBA_INDEXES
<source lang="sql">
SQL> select
2 Tablespace_Name,
3 Owner,
4 COUNT(*)||" tables" Objects
5 from DBA_TABLES
6 where Owner <> "SYS"
7 group by
8 Tablespace_Name,
9 Owner
10 union
11 select
12 Tablespace_Name,
13 Owner,
14 COUNT(*)||" indexes" Objects
15 from DBA_INDEXES
16 where Owner <> "SYS"
17 group by
18 Tablespace_Name,
19 Owner;
TABLESPACE_NAME OWNER OBJECTS
------------ --------------------
SYSAUX CTXSYS 26 tables SYSAUX 47 indexes SYSAUX DBSNMP 17 tables SYSAUX 8 indexes SYSAUX FLOWS_020100 160 tables SYSAUX 422 indexes SYSAUX FLOWS_FILES 1 tables SYSAUX 5 indexes SYSAUX SYSTEM 21 indexes SYSAUX 22 tables SYSAUX TSMSYS 1 tables SYSAUX 2 indexes SYSAUX XDB 11 tables SYSAUX 384 indexes SYSTEM DEFINER 1 indexes SYSTEM INV10 1 indexes SYSTEM INV11 1 indexes SYSTEM INV12 1 indexes SYSTEM INV13 1 indexes SYSTEM INV14 1 indexes SYSTEM INV15 1 indexes SYSTEM INV16 1 indexes SYSTEM INV17 1 indexes SYSTEM INV18 1 indexes SYSTEM INV19 1 indexes SYSTEM INV20 1 indexes SYSTEM sqle 12 indexes SYSTEM 14 tables SYSTEM MDSYS 30 tables SYSTEM 51 indexes SYSTEM OUTLN 3 tables SYSTEM 4 indexes SYSTEM SYSTEM 145 indexes SYSTEM 85 tables USERS HR 19 indexes USERS 6 tables
CTXSYS 11 tables
DBSNMP 2 indexes
4 tables
DEFINER 1 tables
FLOWS_020100 4 tables
HR 1 tables
INV10 1 tables
INV11 1 tables
INV12 1 tables
INV13 1 tables
INV14 1 tables
INV15 1 tables
INV16 1 tables
INV17 1 tables
INV18 1 tables
INV19 1 tables
INV20 1 tables
sqle 3 indexes
9 tables
MDSYS 3 indexes
7 tables
SYSTEM 31 tables
36 indexes
XDB 1 indexes
SQL>
</source>
Union result set in action
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> -- Union result set SQL> SQL> select id, last_name
2 from Employee 3 where last_name like "A%" 4 or last_name like "B%" 5 union 6 select id, last_name 7 from Employee 8 where last_name like "B%" 9 or last_name like "C%";
ID LAST_NAME
----------
05 Black 08 Cat SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Union two complex queries and subquery
<source lang="sql">
SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> create table departments
2 ( deptno NUMBER(2) constraint D_PK 3 primary key 4 , dname VARCHAR2(10) 5 , location VARCHAR2(8) 6 , mgr NUMBER(4) 7 ) ;
Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7); 1 row created. SQL> insert into departments values (20,"TRAINING", "DALLAS", 4); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 6); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 9); 1 row created. SQL> SQL> select d.deptno
2 , d.dname 3 , count(e.empno) as headcount 4 from emp e 5 , departments d 6 where e.deptno = d.deptno 7 group by d.deptno 8 , d.dname 9 union 10 select x.deptno 11 , x.dname 12 , 0 as headcount 13 from departments x 14 where x.deptno not in (select y.deptno 15 from emp y); DEPTNO DNAME HEADCOUNT
---------- ----------
10 ACCOUNTING 3
20 TRAINING 5
30 SALES 6
40 HR 0
SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table departments; Table dropped.
</source>
union with like operator
<source lang="sql">
SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1001 Lawson lawson@g.ru 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.ru 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.ru 01-JAN-02 PROG 3 24000 1004
1004 Kyte tkyte@a.ru 13-JUN-98 MGR 4 25000 1005
1005 Viper sdillon@a .ru 10-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.ru 10-JUN-08 PROG 2 20000
1007 Java java01@g.ru 10-JUN-08 PROG 3 20000 1006
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1008 Oracle wvelasq@g.ru 10-JUN-08 DBA 4 20000 1006
8 rows selected. SQL> SQL> SQL> select employee_id, last_name
2 from employees 3 where last_name like "A%" 4 or last_name like "B%" 5 union 6 select employee_id, last_name 7 from employees 8 where last_name like "B%" 9 or last_name like "C%" 10 /
EMPLOYEE_ID LAST_NAME
--------------------------------------------------
1003 Bliss
1006 Beck
SQL> drop table employees; Table dropped. SQL>
</source>
Using union in subquery
<source lang="sql">
SQL> SQL> CREATE TABLE emp (
2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 );
Table created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SELECT all_dates.emp_date, COUNT(*)
2 FROM (
3 SELECT hire_date emp_date
4 FROM emp
5 UNION ALL
6 SELECT end_date
7 FROM emp) all_dates
8 GROUP BY all_dates.emp_date
9 ORDER BY COUNT(*) DESC;
4
15-NOV-61 2 04-APR-04 2 02-JAN-04 1 03-MAR-04 1 01-APR-04 1 05-MAY-04 1 15-JUN-04 1 30-SEP-04 1 31-OCT-04 1 15-NOV-04 1 01-MAR-94 1 29-DEC-87 1 23-AUG-76 1 16-SEP-64 1 15 rows selected. SQL> SQL> SQL> drop table emp; Table dropped. SQL>
</source>
