Writing Multiple Column Subqueries with table join

    


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> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );

Table created.

SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

1 row created.

SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

1 row created.

SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

1 row created.

SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

1 row created.

SQL>
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-DEC-17        800                    20
      7499 ALLEN      SALESMAN        7698 1981-FEB-20       1600        300         30
      7521 WARD       SALESMAN        7698 1981-FEB-22       1250        500         30
      7566 JONES      MANAGER         7839 1981-APR-02       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-SEP-28       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-MAY-01       2850                    30
      7782 CLARK      MANAGER         7839 1981-JUN-09       2450                    10
      7788 SCOTT      ANALYST         7566 1982-DEC-09       3000                    20
      7839 KING       PRESIDENT            1981-NOV-17       5000                    10
      7844 TURNER     SALESMAN        7698 1981-SEP-08       1500          0         30
      7876 ADAMS      CLERK           7788 1983-JAN-12       1100                    20
      7900 JAMES      CLERK           7698 1981-DEC-03        950                    30
      7902 FORD       ANALYST         7566 1981-DEC-03       3000                    20
      7934 MILLER     CLERK           7782 1982-JAN-23       1300                    10

14 rows selected.

SQL>
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>
SQL>
SQL> -- Writing Multiple Column Subqueries
SQL>
SQL> SELECT empno, deptno
  2  FROM emp
  3  WHERE (empno, deptno) IN
  4    (SELECT e.empno, d.deptno
  5     FROM dept d, emp e);

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7499         30
      7521         30
      7566         20
      7654         30
      7698         30
      7782         10
      7788         20
      7839         10
      7844         30
      7876         20
      7900         30
      7902         20
      7934         10

14 rows selected.

SQL>
SQL>
SQL> drop table dept;

Table dropped.

SQL> drop table emp;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Use sub query as a virtual table
2.	Compare with data from subquery
3.	An example of a nested three-level subquery
4.	If an inner query returns a NULL, the outer query also returns NULL
5.	Working with multi-column subqueries
6.	Use aggregate function in sub query
7.	Sub query: 'SELECT 1 FROM dept d'
8.	Using the EXISTS and NOT EXISTS operators
9.	Writing Single Row Subqueries
10.	Compare with the sub query result
11.	Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
12.	Subqueries in a FROM Clause (Inline Views)
13.	Single Row Subqueries May Return a Maximum of One Row
14.	Subqueries May Not Contain an ORDER BY Clause
15.	Sub query with table join
16.	Subquery just returns a literal value: improve performance of your query
17.	EXISTS typically offers better performance than IN with subqueries
18.	NVL() is used to convert null in correlated query
19.	Writing Nested Subqueries
20.	subqueries in the SELECT column list (New Way)
21.	subqueries in the SELECT column list (Old way)
22.	Not equals and subquery
23.	Greater than average salary
24.	Greater than max(salary)
25.	Larger than value from subquery
26.	Subquery in select statement
27.	Subquery in where clause
28.	Subqueries That Return Multiple Results
29.	Subqueries in the WHERE Clause: equals
30.	Subqueries in the WHERE Clause: less than
31.	Simple Subqueries in select statement
32.	Single-row subqueries return only one row of result.
33.	The parent query of a single-row subquery can return more than one row.
34.	Born after employee 4 was born
35.	inline view: What percentage of these items exist in each bin selected

Writing Multiple Column Subqueries with table join : Subquery Basics « Subquery « Oracle PL / SQL

Related examples in the same category