//
you're reading...
SQL

A Short Guide to Oracle SQL [V] – Joins & SET operators

In this part we talk about Joins and Set operations.

JOIN

Join operation combines rows from two or more tables/views/materialized views. Specifying multiple tables in FROM clause causes, Oracle to perform JOIN operation.

Following example perform a simple JOIN operation.

scott@O11R2>SELECT empno, ename, d.deptno, job, sal, dname, loc
           FROM emp e, dept d
           WHERE e.deptno=d.deptno;

     EMPNO ENAME          DEPTNO JOB              SAL DNAME          LOC
---------- ---------- ---------- --------- ---------- -------------- -------------
      7369 SMITH              20 CLERK            800 RESEARCH       DALLAS
      7499 ALLEN              30 SALESMAN        1600 SALES          CHICAGO
      7521 WARD               30 SALESMAN        1250 SALES          CHICAGO
      7566 JONES              20 MANAGER         2975 RESEARCH       DALLAS
      7654 MARTIN             30 SALESMAN        1250 SALES          CHICAGO
      7698 BLAKE              30 MANAGER         2850 SALES          CHICAGO
      7782 CLARK              10 MANAGER         2450 ACCOUNTING     NEW YORK
      7788 SCOTT              20 ANALYST         3000 RESEARCH       DALLAS
      7839 KING               10 PRESIDENT       5000 ACCOUNTING     NEW YORK
      7844 TURNER             30 SALESMAN        1500 SALES          CHICAGO
      7876 ADAMS              20 CLERK           1100 RESEARCH       DALLAS
      7900 JAMES              30 CLERK            950 SALES          CHICAGO
      7902 FORD               20 ANALYST         3000 RESEARCH       DALLAS
      7934 MILLER             10 CLERK           1300 ACCOUNTING     NEW YORK

14 rows selected.

NOTE:

    • If any of the table in join operation have common column name, the column name must be preceded by the table name.
    • A table alias is work like a column alias temporarily giving a different name to a table.

Cartesian product or CROSS JOIN

If join operation do not have any condition , then oracle performs cross-join. That means each row of one table combined with each row of another table.

scott@O11R2>select count(*) from emp;
  COUNT(*)
----------
        14

scott@O11R2>select count(*) from dept;
  COUNT(*)
----------
         4

scott@O11R2>SELECT empno, ename, d.deptno, job, sal, dname, loc
           FROM emp e, dept d;
           
     EMPNO ENAME          DEPTNO JOB              SAL DNAME          LOC
---------- ---------- ---------- --------- ---------- -------------- -------------
      7369 SMITH              40 CLERK            800 OPERATIONS     BOSTON
      7369 SMITH              30 CLERK            800 SALES          CHICAGO
      7369 SMITH              20 CLERK            800 RESEARCH       DALLAS
      7369 SMITH              10 CLERK            800 ACCOUNTING     NEW YORK
      7499 ALLEN              30 SALESMAN        1600 SALES          CHICAGO
      7499 ALLEN              10 SALESMAN        1600 ACCOUNTING     NEW YORK
      7499 ALLEN              20 SALESMAN        1600 RESEARCH       DALLAS
      7499 ALLEN              40 SALESMAN        1600 OPERATIONS     BOSTON
      7521 WARD               30 SALESMAN        1250 SALES          CHICAGO
      7521 WARD               10 SALESMAN        1250 ACCOUNTING     NEW YORK
      7521 WARD               40 SALESMAN        1250 OPERATIONS     BOSTON
      7521 WARD               20 SALESMAN        1250 RESEARCH       DALLAS
.....
56 rows selected.

The results include (14*4) 56 rows: 14 rows from EMP table and 4 rows from DEPT table.
Cartesian join is rarely useful and combines all rows from one table with all the rows of another table in every possible combination.

Another method to obtain a cross-join between two tables is use of CROSS JOIN keyword. Oracle support CROSS keyword starting with 10g release.

scott@O11R2>SELECT empno, ename, d.deptno, job, sal, dname, loc
           FROM emp e CROSS JOIN dept d;
           
     EMPNO ENAME          DEPTNO JOB              SAL DNAME          LOC
---------- ---------- ---------- --------- ---------- -------------- -------------
      7369 SMITH              40 CLERK            800 OPERATIONS     BOSTON
      7369 SMITH              30 CLERK            800 SALES          CHICAGO
      7369 SMITH              20 CLERK            800 RESEARCH       DALLAS
      7369 SMITH              10 CLERK            800 ACCOUNTING     NEW YORK
      7499 ALLEN              30 SALESMAN        1600 SALES          CHICAGO
      7499 ALLEN              10 SALESMAN        1600 ACCOUNTING     NEW YORK
      7499 ALLEN              20 SALESMAN        1600 RESEARCH       DALLAS
      7499 ALLEN              40 SALESMAN        1600 OPERATIONS     BOSTON
      7521 WARD               30 SALESMAN        1250 SALES          CHICAGO
      7521 WARD               10 SALESMAN        1250 ACCOUNTING     NEW YORK
      7521 WARD               40 SALESMAN        1250 OPERATIONS     BOSTON
      7521 WARD               20 SALESMAN        1250 RESEARCH       DALLAS
.....
56 rows selected.

SELF-JOIN

A self-join is joining a table with itself.

scott@O11R2> SELECT e.empno "Employee ID", e.ename "Employee Name", e.mgr "Manager ID", m.ename "Manager Name"
            FROM emp e, emp m
            WHERE e.mgr=m.empno;
            
Employee ID Employee Name  Manager ID Manager Name
----------- -------------  ---------- ------------
       7369 SMITH                7902 FORD
       7499 ALLEN                7698 BLAKE
       7521 WARD                 7698 BLAKE
       7566 JONES                7839 KING
       7654 MARTIN               7698 BLAKE
       7698 BLAKE                7839 KING
       7782 CLARK                7839 KING
       7788 SCOTT                7566 JONES
       7844 TURNER               7698 BLAKE
       7876 ADAMS                7788 SCOTT
       7900 JAMES                7698 BLAKE
       7902 FORD                 7566 JONES
       7934 MILLER               7782 CLARK

13 rows selected.

Another way to perform self-join is using JOIN…ON keyword.

scott@O11R2> SELECT e.empno "Employee ID", e.ename "Employee Name", e.mgr "Manager ID", m.ename "Manager Name"
            FROM emp e JOIN emp m
            ON e.mgr=m.empno;
            
Employee ID Employee Name  Manager ID Manager Name
----------- -------------  ---------- ------------
       7369 SMITH                7902 FORD
       7499 ALLEN                7698 BLAKE
       7521 WARD                 7698 BLAKE
       7566 JONES                7839 KING
       7654 MARTIN               7698 BLAKE
       7698 BLAKE                7839 KING
       7782 CLARK                7839 KING
       7788 SCOTT                7566 JONES
       7844 TURNER               7698 BLAKE
       7876 ADAMS                7788 SCOTT
       7900 JAMES                7698 BLAKE
       7902 FORD                 7566 JONES
       7934 MILLER               7782 CLARK

13 rows selected.

SIMPLE JOIN, EQUI JOIN, NON-EQUI JOIN, NATURAL JOIN

A simple join is the most common type of join and retrieves rows from two tables having a common column. It is further classified into EQUI JOIN and NON-EQUI JOIN.
An Equi Join is a join in which the joining condition is based on equality between the value of common column.

scott@O11R2> SELECT empno, ename, dname
          FROM emp, dept
          WHERE emp.deptno=dept.deptno;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7566 JONES      RESEARCH
      7654 MARTIN     SALES
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14 rows selected.

scott@O11R2> SELECT empno, ename, dname
          FROM emp, dept
          WHERE emp.deptno=dept.deptno;
          AND sal > 2000;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7566 JONES      RESEARCH
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7902 FORD       RESEARCH

6 rows selected.

Natural Join

Join all columns that have the same name in all tables. The columns must have the same data types or you will receive an error. We can’t use Natural join if columns have different names.

scott@O11R2> SELECT ename, job, dname
          FROM emp NATURAL JOIN dept;

ENAME      JOB       DNAME
---------- --------- --------------
SMITH      CLERK     RESEARCH
ALLEN      SALESMAN  SALES
WARD       SALESMAN  SALES
JONES      MANAGER   RESEARCH
MARTIN     SALESMAN  SALES
BLAKE      MANAGER   SALES
CLARK      MANAGER   ACCOUNTING
SCOTT      ANALYST   RESEARCH
KING       PRESIDENT ACCOUNTING
TURNER     SALESMAN  SALES
ADAMS      CLERK     RESEARCH
JAMES      CLERK     SALES
FORD       ANALYST   RESEARCH
MILLER     CLERK     ACCOUNTING

14 rows selected.

A natural join can be cause of unexpected results if the column have same name but aren’t related to each other.

JOIN…USING

USING clause join two tables using the column(s) specified. The column name(s) has the same definition and MUST match to use this syntax.

scott@O11R2> SELECT empno "Emp. ID", ename "Employee", job, dname "Department"
           FROM emp JOIN dept
           USING (deptno);

   Emp. ID Employee   JOB       Department
---------- ---------- --------- --------------
      7369 SMITH      CLERK     RESEARCH
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7782 CLARK      MANAGER   ACCOUNTING
      7788 SCOTT      ANALYST   RESEARCH
      7839 KING       PRESIDENT ACCOUNTING
      7844 TURNER     SALESMAN  SALES
      7876 ADAMS      CLERK     RESEARCH
      7900 JAMES      CLERK     SALES
      7902 FORD       ANALYST   RESEARCH
      7934 MILLER     CLERK     ACCOUNTING

14 rows selected.

--The above example can be written using INNER keyword.

scott@O11R2> SELECT empno, ename, job, dname
          FROM emp INNER JOIN dept
          USING (deptno);

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7369 SMITH      CLERK     RESEARCH
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7782 CLARK      MANAGER   ACCOUNTING
      7788 SCOTT      ANALYST   RESEARCH
      7839 KING       PRESIDENT ACCOUNTING
      7844 TURNER     SALESMAN  SALES
      7876 ADAMS      CLERK     RESEARCH
      7900 JAMES      CLERK     SALES
      7902 FORD       ANALYST   RESEARCH
      7934 MILLER     CLERK     ACCOUNTING

14 rows selected.

Any columns in USING clause appears only once and without any qualifier. Specify any qualifier throw ORA-25154 error.

JOIN… ON

If two tables have a common column but don’t share common names, use ON clause with JOIN keyword.

SQL> SELECT s_first_name||' '||s_last_name "Student", cl_project_code "Project No", cl_project_title "Project Name"
     FROM student JOIN class_project
     ON student.studentid = class_project.cl_proj_stu_id;

Student                         Proje Project Name
------------------------------- ----- ----------------------------------------------------------------------
Sabrina Khan                    PR30A Web-based Resource Allocation and Problem Tracking System
John King                       PR01A Database Development for the WWW
Prakash Nanda                   PR02A Online Database for Non Profit Organisation
Anil Ray                        PR03A An email filter to cut out spam
Chris Daves                     PR04A Tools for measuring database performance
Varun Naidu                     PR05A Development of a game-playing program
Satish Paul                     PR06A On-line Museum Catalogue
Rajit Nair                      PR07A Airline Flight Information System
Keith Peters                    PR08A Absence Booking System
Rucha Sharma                    PR09A Web Based Achievement Chart
Deepti Dutt                     PR10A Court Reservation System
Akshat Verma                    PR11A Comparing JPEG files for similarity
Sarah Neilson                   PR12A A real-time online time diary
Siddhartha Malhotra             PR13A Designing an accessible public information system
Ellie Grace                     PR14A Web site for a small company
Yusuf Hussain                   PR15A An Online Conference Management System
Steve Kapoor                    PR16A A Library of Historic Photographs
Sameer George                   PR17A E-school
George Thomson                  PR18A Job recruitment system
John Scott                      PR19A Emergency Call System
Clara Liu                       PR20A Financial Calculator
Paul Smith                      PR21A  Travellers' Tales: a web-site for sharing stories
Jessica Nicholas                PR22A Allocating students to accommodation on campus
Abhishek Iyer                   PR23A Wildlife Monitoring Database
Samantha Roy                    PR24A Shopping Centre Information System
Tanya Singh                     PR25A An Online Conference Management System
Arvind Krishna                  PR26A A System for Scoring Cricket Matches
Emily Kim                       PR27A Bug Tracking System
Donna Baker                     PR28A FAQ System
Vedant Malik                    PR29A Bibliographic Database System
Rick Irwin                      PR31A Web-Based Demonstration of Normalisation

31 rows selected.

Non-Equi Joins

A non equi-join specifies the relationship between columns of different tables by making use of the relational operator other than equal to.

scott@O11R2> SELECT ename, job, sal, grade
           FROM emp, salgrade
           WHERE sal BETWEEN losal AND hisal;
ENAME      JOB              SAL      GRADE
---------- --------- ---------- ----------
SMITH      CLERK            800          1
JAMES      CLERK            950          1
ADAMS      CLERK           1100          1
WARD       SALESMAN        1250          2
MARTIN     SALESMAN        1250          2
MILLER     CLERK           1300          2
TURNER     SALESMAN        1500          3
ALLEN      SALESMAN        1600          3
CLARK      MANAGER         2450          4
BLAKE      MANAGER         2850          4
JONES      MANAGER         2975          4
SCOTT      ANALYST         3000          4
FORD       ANALYST         3000          4
KING       PRESIDENT       5000          5

14 rows selected.

--using JOIN keyword
scott@O11R2> SELECT ename, job, sal, grade
          FROM emp JOIN salgrade
          ON sal BETWEEN losal AND hisal;

ENAME      JOB              SAL      GRADE
---------- --------- ---------- ----------
SMITH      CLERK            800          1
JAMES      CLERK            950          1
ADAMS      CLERK           1100          1
WARD       SALESMAN        1250          2
MARTIN     SALESMAN        1250          2
MILLER     CLERK           1300          2
TURNER     SALESMAN        1500          3
ALLEN      SALESMAN        1600          3
CLARK      MANAGER         2450          4
BLAKE      MANAGER         2850          4
JONES      MANAGER         2975          4
SCOTT      ANALYST         3000          4
FORD       ANALYST         3000          4
KING       PRESIDENT       5000          5

14 rows selected.

OUTER Joins

An outer join returns all the rows returned by an simple join (inner, equi etc.) as well as those rows from one table that do not have any corresponding rows from the other table. An outer join is used to retrieve the rows with an unmatched value in the relevant column.

hr@O11R2> SELECT first_name, last_name,salary, job_id, department_name, d.department_id
          FROM employees e, departments d 
          WHERE e.department_id(+) =d.department_id;

FIRST_NAME           LAST_NAME           SALARY JOB_ID     DEPARTMENT_NAME                DEPARTMENT_ID
-------------------- --------------- ---------- ---------- ------------------------------ -------------
Jennifer             Whalen                4400 AD_ASST    Administration                            10
Michael              Hartstein            13000 MK_MAN     Marketing                                 20
Pat                  Fay                   6000 MK_REP     Marketing                                 20
Den                  Raphaely             11000 PU_MAN     Purchasing                                30
Karen                Colmenares            2500 PU_CLERK   Purchasing                                30
Alexander            Khoo                  3100 PU_CLERK   Purchasing                                30
Shelli               Baida                 2900 PU_CLERK   Purchasing                                30
Sigal                Tobias                2800 PU_CLERK   Purchasing                                30
....
....
William              Smith                 7400 SA_REP     Sales                                     80
Tayler               Fox                   9600 SA_REP     Sales                                     80
Harrison             Bloom                10000 SA_REP     Sales                                     80
Lisa                 Ozer                 11500 SA_REP     Sales                                     80
John                 Russell              14000 SA_MAN     Sales                                     80
Karen                Partners             13500 SA_MAN     Sales                                     80
....
....
Sundar               Ande                  6400 SA_REP     Sales                                     80
Amit                 Banda                 6200 SA_REP     Sales                                     80
Neena                Kochhar              17000 AD_VP      Executive                                 90
Steven               King                 24000 AD_PRES    Executive                                 90
Lex                  De Haan              17000 AD_VP      Executive                                 90
John                 Chen                  8200 FI_ACCOUNT Finance                                  100
Nancy                Greenberg            12008 FI_MGR     Finance                                  100
....
....
William              Gietz                 8300 AC_ACCOUNT Accounting                               110
Shelley              Higgins              12008 AC_MGR     Accounting                               110
                                                           Treasury                                 120
                                                           Corporate Tax                            130
                                                           Control And Credit                       140
                                                           Shareholder Services                     150
                                                           Benefits                                 160
                                                           Manufacturing                            170
                                                           Construction                             180
                                                           Contracting                              190
                                                           Operations                               200
                                                           IT Support                               210
                                                           NOC                                      220
                                                           IT Helpdesk                              230
                                                           Government Sales                         240
                                                           Retail Sales                             250
                                                           Recruiting                               260
                                                           Payroll                                  270

122 rows selected.

(+) represents outer join and can appear only on one side of the expression – the side that has matching rows missing.

LEFT OUTER JOIN

In Left outer join between two tables contains all records of the table to the LEFT of the clause even if it find no matches with the table listed on the right.

hr@O11R2>SELECT first_name, last_name, department_name
        FROM employees e LEFT OUTER JOIN departments d
        USING(department_id);
FIRST_NAME           LAST_NAME       DEPARTMENT_NAME
-------------------- --------------- ----------------------------
Jennifer             Whalen          Administration
Pat                  Fay             Marketing
Michael              Hartstein       Marketing
Karen                Colmenares      Purchasing
Guy                  Himuro          Purchasing
....
Den                  Raphaely        Purchasing
Susan                Mavris          Human Resources
Kevin                Feeney          Shipping
Alana                Walsh           Shipping
Britney              Everett         Shipping
.....
Douglas              Grant           Shipping
Donald               OConnell        Shipping
Diana                Lorentz         IT
Valli                Pataballa       IT
Alexander            Hunold          IT
Hermann              Baer            Public Relations
Charles              Johnson         Sales
Jack                 Livingston      Sales
.....
Lex                  De Haan         Executive
Neena                Kochhar         Executive
Steven               King            Executive
Luis                 Popp            Finance
Daniel               Faviet          Finance
Nancy                Greenberg       Finance
William              Gietz           Accounting
Shelley              Higgins         Accounting
Kimberely            Grant

107 rows selected.

Thus the LEFT OUTER JOIN includes unmatched rows from the first (left) table but does not include any unmatched rows from the second (right) table.

RIGHT OUTER JOIN

It performs opposite of the Left outer join. A Right outer join between two tables contains all unmatched rows from the right table but does not include any unmatched rows from the left table.

hr@O11R2> SELECT first_name, last_name, department_name
       FROM employees e RIGHT OUTER JOIN departments d
       USING(department_id);

FIRST_NAME           LAST_NAME       DEPARTMENT_NAME
-------------------- --------------- ------------------------------
Jennifer             Whalen          Administration
Pat                  Fay             Marketing
Michael              Hartstein       Marketing
...
Guy                  Himuro          Purchasing
Susan                Mavris          Human Resources
Kevin                Feeney          Shipping
...
Britney              Everett         Shipping
Jennifer             Dilly           Shipping
Julia                Dellinger       Shipping
Curtis               Davies          Shipping
Kelly                Chung           Shipping
..
David                Austin          IT
Valli                Pataballa       IT
Bruce                Ernst           IT
Diana                Lorentz         IT
Alexander            Hunold          IT
Hermann              Baer            Public Relations
Ellen                Abel            Sales

Alberto              Errazuriz       Sales
Tayler               Fox             Sales
Danielle             Greene          Sales
Peter                Hall            Sales
Alyssa               Hutton          Sales
....
Neena                Kochhar         Executive
Steven               King            Executive
Lex                  De Haan         Executive
Luis                 Popp            Finance
....
Nancy                Greenberg       Finance
William              Gietz           Accounting
Shelley              Higgins         Accounting
                                     Treasury
                                     Corporate Tax
                                     Control And Credit
                                     Shareholder Services
                                     Benefits
                                     Manufacturing
                                     Construction
                                     Contracting
                                     Operations
                                     IT Support
                                     NOC
                                     IT Helpdesk
                                     Government Sales
                                     Retail Sales
                                     Recruiting
                                     Payroll

122 rows selected.

FULL OUTER JOIN

It includes all the rows from both tables. It combines the effect of both Left outer join and Right outer join. Full Outer Join is supported by Oracle started with 9i release.

hr@O11R2> SELECT first_name, last_name, department_name
     FROM employees e FULL OUTER JOIN departments d
     USING(department_id);

FIRST_NAME           LAST_NAME       DEPARTMENT_NAME
-------------------- --------------- ------------------------------
Donald               OConnell        Shipping
Jennifer             Whalen          Administration
Michael              Hartstein       Marketing
Susan                Mavris          Human Resources
Hermann              Baer            Public Relations
Shelley              Higgins         Accounting
William              Gietz           Accounting
Lex                  De Haan         Executive
Alexander            Hunold          IT
Bruce                Ernst           IT
David                Austin          IT
Valli                Pataballa       IT
Diana                Lorentz         IT
Nancy                Greenberg       Finance
Jose Manuel          Urman           Finance
Luis                 Popp            Finance
Den                  Raphaely        Purchasing
Karen                Colmenares      Purchasing
Matthew              Weiss           Shipping
Adam                 Fripp           Shipping
Payam                Kaufling        Shipping
.....
Jonathon             Taylor          Sales
Jack                 Livingston      Sales
Charles              Johnson         Sales
Winston              Taylor          Shipping
Jean                 Fleaur          Shipping
.....
Kevin                Feeney          Shipping
Kimberely            Grant
                                     NOC
                                     Manufacturing
                                     Government Sales
                                     IT Support
                                     Benefits
                                     Shareholder Services
                                     Retail Sales
                                     Control And Credit
                                     Recruiting
                                     Operations
                                     Treasury
                                     Payroll
                                     Corporate Tax
                                     Construction
                                     Contracting
                                     IT Helpdesk

123 rows selected.

SET Operator

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries. Remember that the two queries must result in the same number of columns and compatible data types in order to perform set operations.

UNION

Union operation returns all distinct rows returned by the two queries.

scott@O11R2> SELECT * FROM emp
        WHERE sal > 2000;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

6 rows selected.

scott@O11R2> SELECT * FROM emp
        WHERE deptno = 20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

scott@O11R2> SELECT * FROM emp
         WHERE sal > 2000
         UNION
         SELECT * FROM emp
         WHERE deptno = 20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

8 rows selected.

UNION ALL

Union All returns all the rows of the two queries, including duplicates.

scott@O11R2> SELECT * FROM emp
         WHERE sal > 2000
         UNION ALL
         SELECT * FROM emp
         WHERE deptno = 20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

11 rows selected.

INTERSECT

The INTERSECT returns the common row from two sets of query results.

scott@O11R2> SELECT * FROM emp
         WHERE sal > 2000
         INTERSECT
         SELECT * FROM emp
         WHERE deptno = 20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

MINUS

MINUS returns all distinct rows returned by the first query but not the second.

scott@O11R2> SELECT * FROM emp
         WHERE sal > 2000
         MINUS
         SELECT * FROM emp
         WHERE deptno = 20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

REFERENCE:
Oracle Database SQL Language Reference 11g Release 2
Oracle Database SQL Language Reference – SET operators

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: