Retrieving Data using Subqueries

  1. Multiple Column Sub – Query

Each row of the main query is compared to values from a Multiple – Row and Multiple – Column Subquery.

In Single row and multiple row subqueries the inner SELECT statement will return only one
col and this is used to evaluate the expression in the parent SELECT statement.

If you want to compare two or more cols you must write a compound WHERE Clause using
logical operators.

In order to avoid those we can use the multiple col subqueries and duplicate WHERE Clause
into a single WHERE Clause.

  1. Column Comparisons

Multiple Column comparisons involving subqueries can be :
Nonpairwise comparisons – Check each col individually
Pairwise comparisons – Check two cols simultaneously

  1. Pair Wise Comparison Subquery

Ex.

— SQL Query to Display the details of Employees who are managed by the same manager and work in the same department as Employees with the first name as ‘John’ —

SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME = ‘John’
/

SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FIRST_NAME = ‘John’
/

SELECT FIRST_NAME,LAST_NAME,MANAGER_ID,DEPARTMENT_ID
FROM EMPLOYEES WHERE
(MANAGER_ID,DEPARTMENT_ID) IN(SELECT MANAGER_ID,DEPARTMENT_ID FROM EMPLOYEES WHERE FIRST_NAME = ‘John’)
/

  1. The Inner Query will retrieve the MANAGER_ID and DEPARTMENT_ID values for the Employees with the First Name ‘John’.
  2. These combination values are compared with the MANAGER_ID and DEPARTMENT_ID column of each row from the Employees Table.
    If the combination matches then that row is displayed.

— Oracle Server runs the Query in the following way —
SELECT FIRST_NAME,LAST_NAME,MANAGER_ID,DEPARTMENT_ID
FROM EMPLOYEES WHERE
(MANAGER_ID,DEPARTMENT_ID) IN(
108,100/123,50/100,80)
/

  1. Non Pair Wise Comparisons

Ex.

— SQL Query to Display the details of Employees who are managed by the same manager as the employees with the First Name as ‘John’ and work in the same department as the Employees with the First Name as ‘John’ —

SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME = ‘John’
/

SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FIRST_NAME = ‘John’
/

SELECT
FIRST_NAME,
LAST_NAME,
MANAGER_ID,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE
MANAGER_ID IN(SELECT MANAGER_ID FROM EMPLOYEES WHERE FIRST_NAME = ‘John’)
AND
DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FIRST_NAME = ‘John’)
/

  1. The First INNER Subquery to retrive the MANAGER_ID values for the Employee ‘John’ is executed.
  2. The Second INNER Subquery to retrive the DEPARTMENT_ID values for the Employee ‘John’ is executed.
  3. The retrived values of MANAGER_ID are compared with the MANAGER_ID returned by the Main Query. If records are matched then it returns those records.
  4. The retrived values of DEPARTMENT_ID are compared with the DEPARTMENT_ID
    returned by the Main Query. If records are matched then it returns those records.

— Oracle Server runs the Query in the following way —
SELECT FIRST_NAME,LAST_NAME,MANAGER_ID,DEPARTMENT_ID
FROM EMPLOYEES WHERE
MANAGER_ID IN(108,123,100)
AND
DEPARTMENT_ID IN(100,50,80)
/

  1. Scalar Subquery Expressions

A Scalar Subquery is a subquery that returns exactly one col value from one row.

Scalar Subqueries can be used in :

a. Condition and expression part of DECODE and Case
b. All clauses of SELECT except GROUP by.

These allow you to embed SQL Statements which return a scalar value within SQL Statements otherwise known as
SELECTING a Select. An alternative for OUTER Joins.

  1. Scalar Subqueries : Examples

a. Scalar Subqueries in CASE expressions :

— SQL Query to mention the Country Name to Canada for Deptno 20 and for rest of the Departments as USA using Scalar Subqueries in CASE —

SELECT EMPNO,ENAME,DEPTNO,
(CASE WHEN DEPTNO =
(SELECT DEPTNO FROM DEPT WHERE DEPTNO = 20)
THEN ‘Canada’ ELSE ‘USA’ END) LOCATION
FROM EMP
/

b. Scalar Subqueries in ORDER BY Clause

— SQL Query to sort the Records by Department No. based on Scalar Subqueries in ORDER BY Clause —
SELECT EMPNO,ENAME,DEPTNO
FROM EMP E
ORDER BY
(SELECT DEPTNO
FROM DEPT D
WHERE E.DEPTNO = D.DEPTNO)
/

— Other Examples —

— Query written using LEFT OUTER JOIN — (Reduces the performance depending on the complexity of the OUTER JOIN)
SELECT emp.empno,emp.deptno,dept.dname FROM
emp LEFT OUTER JOIN dept ON (dept.deptno = emp.deptno)
/

— Query written using SCALAR SUBQUERY —

SELECT emp.empno,emp.deptno,
(SELECT dept.dname FROM dept WHERE dept.deptno = emp.deptno) join
FROM emp order by deptno
/

DROP TABLE t1
/

DROP TABLE t2
/

  1. Correlated Subquery

These subqueries are used for row-by-row processing.
Each subquery is executed once for every row of the outer query.

Nested Subquery versus Correlated Subquery :

With a normal nested subquery the inner SELECT query runs first and executes once returning
values to be used by the main query.
But a correlated subquery executes once for each row considered by the outer query.
The inner query is driven by the outer query.

Nested Subquery execution :
a.The inner query executes first and returns a value.
b.The outer query executes once using the value from the inner query.

Get a row from the main query
Execute the inner query using the value returned from the main query
USe the values resulting from the inner query to qualify or disqualify the row returned from the main query.
Repeat the process of qualifying until all rows are qualified with the outer query.

The Subquery references a col from a table in the parent query.

8.Using Correlated Subqueries

— Query to find all employees who earn more than the average salary in their department —

SELECT AVG(SAL),DEPTNO FROM EMP
GROUP BY DEPTNO
/

SELECT EMPNO,ENAME,DEPTNO,SAL
FROM EMP OUTER
WHERE SAL > (SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = OUTER.DEPTNO)
/

SELECT EMPNO,ENAME,DEPTNO,SAL
FROM EMP OUTER
WHERE SAL < (SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = OUTER.DEPTNO)
/

  1. Exists Operator

The EXISTS Operator tests for existence of rows in the results set of the subquery.
If a subquery row is found :
The search does not continue in the inner query.
The condition is returned as TRUE.

If a subquery row is not found
The condition is returned as FALSE
The search continues in the inner query

Accordingly NOT EXISTS tests whether a value retrieved by the outer query is not part of the
result set of the values retrieved by the inner query.

If the EXISTS operator is used the SQL engine will stop the scanning process as soon as it finds a match.

  1. Find Employees who have at least one person reporting to them

— Query to find employees who have atleast one person reporting to them —
SELECT EMPNO,ENAME,MGR,JOB,DEPTNO
FROM EMP
OUTER
WHERE EXISTS
(SELECT ‘X’ FROM EMP
WHERE MGR = OUTER.EMPNO)
ORDER BY DEPTNO
/

Note that the inner SELECT Query does not need to return a specific value to the Outer Query so a constant can be selected.

— Query to find department details that have employees assigned to —
SELECT DEPTNO,DNAME
FROM DEPT D
WHERE EXISTS
(SELECT ‘X’ FROM EMP WHERE DEPTNO = D.DEPTNO)
/

— NULL can also be used as a CONSTANT in EXISTS Operator —
SELECT DEPTNO,DNAME
FROM DEPT D
WHERE EXISTS
(SELECT NULL FROM EMP WHERE DEPTNO = D.DEPTNO)
/

  1. Find all Departments that do not have any Employees

— Query to find department details that do not have any employees assigned to —
SELECT DEPTNO,DNAME
FROM DEPT D
WHERE NOT EXISTS
(SELECT ‘X’ FROM EMP WHERE DEPTNO = D.DEPTNO)
/

  1. Correlated Update

Use correlated subquery to update rows in one table based on rows from another table.
SELECT * FROM DEPT
/

CREATE TABLE DEPT16 AS SELECT * FROM DEPT
/

SELECT * FROM DEPT16
/

ALTER TABLE DEPT16 ADD(CONCAT_LOC VARCHAR2(50))
/

DESC DEPT16;

UPDATE DEPT16 E
SET CONCAT_LOC =
(SELECT CONCAT(LOC,DNAME) FROM DEPT D
WHERE E.DEPTNO = D.DEPTNO)
/

SELECT * FROM DEPT16
/

DROP TABLE DEPT16 PURGE
/

  1. Correlated Delete

Use Correlated delete to delete rows in one table based on rows from another table.

SELECT * FROM DEPT
/

CREATE TABLE DEPT162 AS SELECT * FROM DEPT
/

INSERT INTO DEPT162 VALUES(51,’ju’,’hj’)
/

DELETE FROM DEPT162 E
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT WHERE DEPTNO = E.DEPTNO)
/

SELECT * FROM DEPT162
/

DROP TABLE DEPT162
/

  1. WITH Clause

WITH Clause can be used to reduce repetition and simplify complex SQL statements.
The WITH query_name clause lets us to assign a name to a subquery block.
You can then reference the subquery block multiple places in the query by specifying the query name.

A WITH Clause is really best used when the result of the WITH query is required more than one time in the body of the query Ex. A average value needs to be compared against two or three times.

a. Single Aliases
Ex.

WITH q AS (SELECT dummy FROM DUAL)
SELECT dummy FROM q
/

— Query to display for each Employee how many People are there in their departments —
SELECT
E.ENAME AS “Employee Name”,
E.DEPTNO,
dc.dept_count as EMP_DEPT_COUNT
FROM
EMP E,
(SELECT DEPTNO,COUNT(*) AS DEPT_COUNT
FROM EMP
GROUP BY DEPTNO) DC
WHERE E.DEPTNO = DC.DEPTNO
/

WITH DEPT_COUNT AS (
SELECT DEPTNO,COUNT(*) AS DEPT_COUNT
FROM EMP
GROUP BY DEPTNO)
SELECT E.ENAME AS ENAME,DC.DEPT_COUNT
AS COUNT
FROM EMP E,
DEPT_COUNT DC
WHERE E.DEPTNO = DC.DEPTNO
/

© 2024 AWS. Learn from Scratch