×
☰ See All Chapters

Oracle equi join

A sub query, also known as a nested query, is a query embedded within the WHERE clause or HAVING clause of another query to further restrict data returned by the query. We can use sub query in FROM clause for Oracle. For MySQl we can use sub query in FROM clause if column aliases are used. When you have a subquery in a query, the subquery is resolved first, and then the main query is resolved according to the condition(s) resolved by the subquery. The same rules that apply to standard queries also apply to subqueries. You can use join operations, functions, conversions, and other options within a subquery.

Subqueries must follow a few below rules

  1. Subqueries must be enclosed within parentheses. 

  2. A subquery can have only one column in the SELECT clause, main query can have any number of columns. 

  3. ORDER BY clause can be used in subquery. You can use the GROUP BY clause to perform the same function as the ORDER BY clause in a subquery. 

  4. A subquery should return more than one row.  

  5. The SELECT list cannot include references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB. 

  6. You cannot immediately enclose a subquery in a SET function. 

  7. You cannot use the BETWEEN operator with a subquery; however, you can use the BETWEEN operator within the subquery.  

Subqueries examples

Creating table for demonstrating subqueries

CREATE TABLE DEPT

       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

        DNAME VARCHAR2(14) ,

        LOC VARCHAR2(13) ) ;

 

CREATE TABLE EMP

       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7,2),

        COMM NUMBER(7,2),

        DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

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

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

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

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

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

COMMIT;

Example 1:

SELECT ENAME, SAL FROM EMP WHERE SAL =    

(SELECT MAX(SAL) FROM EMP WHERE SAL <

(SELECT MAX(SAL) FROM EMP))

oracle-sub-queries-0

Example 2:

SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO IN

(SELECT DEPTNO FROM DEPT WHERE LOC IN('NEW YORK','DALLAS'))

oracle-sub-queries-1

Example 3:

SELECT DEPTNO, MIN(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING MIN(SAL) > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20)

ORDER BY MIN(SAL)

oracle-sub-queries-2
 

Example 4:

SELECT DEPTNO, MIN(SAL)

FROM (SELECT * FROM EMP WHERE DEPTNO IS NOT NULL)

GROUP BY DEPTNO

HAVING MIN(SAL) >(SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20)

oracle-sub-queries-3
 

Example 5:

SELECT ENAME, JOB, SAL FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP

WHERE DEPTNO = 20)

oracle-sub-queries-4
 

Example 6:

SELECT ENAME, JOB, SAL FROM EMP WHERE SAL > ANY(SELECT SAL FROM EMP

WHERE DEPTNO = 20) AND DEPTNO <> 20

oracle-sub-queries-5
 

Example 7:

CREATE TABLE EMPLOYEE AS(SELECT * FROM EMP)

Example 8:

CREATE TABLE EMPLOYEE AS (SELECT * FROM EMP where 1=2)

 

 

All Chapters
Author