SubQueries in Oracle
(1) A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is a query within a query
(2) A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
(3) Subqueries answer the queries that have multiple parts. The parent query answers a part and the sub query answers other part
(5)Using subqueries in a FROM clause is known as an inline view.
(6) Using subqueries in the WHERE clause is called a nested subquery. Up to 255 nested queries are allowed.
Some Guidelines for Oracle SubQueries
(1) We need to put sub queries in parenthesis always
(2) We need to place subqueries on the right side of the comparison operator
(3) Use single row operator with single row subqueries and multiple row operator with multiple row subqueries
SELECT col1, col2 FROM table1 WHERE col1 OPERATOR (SELECT col1 FROM table2 [WHERE])
Single Row Subquery
It returns only one row of results and uses a single row operator (most common is the equal operator (=)). The other operators are > ,< ,>= ,=<
Single row subqueries can select data from the same table or from another table
SELECT ENAME FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP);SELECT ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'RESEARCH');
Multiple Row Subquery
It returns several rows of results from the subquery, uses the IN operator. In the previous query, if there was more than one research department, the query would have failed. Example of returning more than one row in the subquery
The other operator which are used is any and all
SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME LIKE 'R%');
A correlated subquery is a subquery that relies on columns from the parent query. A correlated subquery is evaluated for each row processed by the parent query. The parent statement can be a SELECT, UPDATE or DELETE.
SELECT ENAME,SAL FROM EMP E1 WHERE SAL = (SELECT MAX(SAL) FROM EMP E2 WHERE E1.DEPTNO = E2.DEPTNO);
Based on the same data of emp and dept ,here is the result
SELECT ENAME, DEPTNO, (SELECT MAX(SAL) FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO) HIGH_SAL FROM EMP E1 WHERE ENAME LIKE 'B%';
The sub query executes once for each execution of the master query. A single-row subquery can only be used with single-row operators
The query can have more than one column in the SELECT clause of a subquery or in UPDATE statements
SELECT CITY,CITY_CODE,CITY_DESCRIPTION FROM LOCATIONS WHERE (LOCATION_ID, COUNTRY_ID) IN (SELECT LOCATION_ID, COUNTRY_ID FROM LOCATIONS WHERE STATE_PROVINCE = 'NEWYORK');
Subqueries in other DML Statements
Subqueries can be used in UPDATE, DELETE and INSERT statements
Update Statement UPDATE EMP SET SALARY = (SELECT SALARY FROM EMP WHERE EMPLOYEE_ID = 112408) WHERE EMPLOYEE_ID = 193711; Insert Statement INSERT INTO DEPT (DEPT_ID, DEPT_NAME) VALUES ((SELECT MAX(DEPT_ID) FROM DEPT), 'NEW DEPT'); Delete Statement DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM CUST_BACK WHERE AGE > 40 );