What
are Sub-Queries Scalar and Co-Related Sub Queries in ORACLE?
Sub
Query (Query within another Query)
Subquery
executes only once and gives the output to outer query then outer query
executes). So subquery returning one output value and based on that output
value outer query is going to be executed.
Types-
Single
row Sub Query (Single output row)
The Subquery returns only one row. Use single row comparison
operators like =, > etc while doing comparisions.
Example of Single Row Subquery
Find the highest salary:
Find the highest salary:
SELECT
last_name, salary
FROM employees
WHERE salary = (Select MAX(salary) FROM employees);
FROM employees
WHERE salary = (Select MAX(salary) FROM employees);
Multi
row Sub Query (Multiple output rows)
The subquery returns more than one row. Use multiple row
comparison operators like IN, ANY, ALL in the comparisons.
Example
of Multiple Row Subquery
SELECT employee_id, last_name, salary
FROM employees
WHERE salary IN (SELECT Min(salary)
FROM employees
GROUP BY department_id);
SELECT employee_id, last_name, salary
FROM employees
WHERE salary IN (SELECT Min(salary)
FROM employees
GROUP BY department_id);
Single
Column Subquery
Example
of single column subquery
A query to retrieve the name of highest paid person in the department 30
Please see that ALL is used with comparison operator because subquery is not returning single value, It is returning multiple values.
A query to retrieve the name of highest paid person in the department 30
Please see that ALL is used with comparison operator because subquery is not returning single value, It is returning multiple values.
Select
first_name, last_name
from employee
where dept_id =30 and salary >=ALL (Select salary
from employee
where dept_id = 30);
from employee
where dept_id =30 and salary >=ALL (Select salary
from employee
where dept_id = 30);
Multiple
Column Subquery
Example
of Multiple column subquery
A subquery that compares more than just one column between the parent query and the subquery is called multiple-column subquery.
Extract employees that make the same salaries as other employee with employee_id 420 with the same job
Select employee_ID, last_name, job_id, salary
from employees
where (job_id, salary) in (select job_id, salary
from employees
where employee_id =420);
A subquery that compares more than just one column between the parent query and the subquery is called multiple-column subquery.
Extract employees that make the same salaries as other employee with employee_id 420 with the same job
Select employee_ID, last_name, job_id, salary
from employees
where (job_id, salary) in (select job_id, salary
from employees
where employee_id =420);
Single Row Subquery Examples
1. Write a query to find the salary of employees whose salary is
greater than the salary of employee whose id is 100?
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY >
(
SELECT
SALARY
FROM
EMPLOYEES
WHERE
EMPLOYEED_ID = 100
)
2.
Write a query to find the employees who all are earning the highest salary?
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY =
(
SELECT MAX(SALARY)
FROM
EMPLOYEES
)
3. Write a query to find
the departments in which the least salary is greater than the highest salary in
the department of id 200?
SELECT DEPARTMENT_ID,
MIN(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) >
(
SELECT
MAX(SALARY)
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = 200)
Multiple Row Subquery Examples
1. Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 30?
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY IN
(
SELECT
SALARY
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = 30
)
2. Write a query to find
the employees whose salary is greater than at least on employee in department
of id 50?
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY > ANY
(SELECT
SALARY
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = 50)
3. Write a query to find
the employees whose salary is less than the salary of all employees in
department of id 10?
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY < ALL
(
SELECT
SALARY
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = 10
)
4. Write a query to find
the employees whose manager and department should match with the employee of id
20 or 30?
SELECT EMPLOYEE_ID,
MANAGER_ID,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE (MANAGER_ID,DEPARTMENT_ID) IN
(
SELECT
MANAGER_ID,
DEPARTMENT_ID
FROM
EMPLOYEES
WHERE
EMPLOYEE_ID IN (20,30)
)
5. Write a query to get
the department name of an employee?
SELECT EMPLOYEE_ID,
DEPARTMENT_ID,
(SELECT
DEPARTMENT_NAME
FROM
DEPARTMENTS D
WHERE D.DEPARTMENT_ID
= E.DEPARTMENT_ID
)
FROM EMPLOYEES E
Co-Related
Sub Queries
If
the subquery depends on the output generated by the outer query in that case
subquery is called as Co-related sub queries.
A Correlated
Subquery is one that is executed after the outer query is executed. So
correlated subqueries take an approach opposite to that of normal subqueries.
The correlated subquery execution is as follows:
· The outer query receives a row.
· For each candidate row of the outer query, the
subquery (the correlated subquery) is executed once.
· The results of the correlated subquery are used to
determine whether the candidate row should be part of the result set.
· The process is repeated for all rows.
Get the Department details
having no Employee at All.
Get the product details not
sold to a single customer.
In
above two cased we have to go for Co-related sub queries.
Co-related
sub queries make use of Exist/ Not Exists operator.
Select
----- from T1
Where
Exists/Not Exists (Select ----- from T2 where T1.PK=T2.FK);
It is not recommended to use
Co-related sub queries as it slow down the performance.
Get the Department details
having at least one Employee.
Select
* from Department D
Where
Exists (Select Empno from Employee E where E.Deptno=D.Deptno);
Get the Department details
having no Employee at All.
Select
* from Department D
Where
Not Exists (Select Empno from Employee E where E.Deptno=D.Deptno);
1. Write a query to find the highest earning employee in each department?
SELECT DEPARTMENT_ID,
EMPLOYEE_ID,
SALARY
FROM EMPLOYEES E_0
WHERE 1 =
(
SELECT COUNT(DISTINCT SALARY)
FROM
EMPLOYEES E_I
WHERE
E_O.DEPARTMENT_ID = E_I.DEPARTMENT_ID
AND
E_O.SALARY <= E_I.SALARY )
2. Write a query to list the department
names which have at least one employee?
SELECT DEPARTMENT_ID,
DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE EXISTS
(
SELECT 1
FROM
EMPLOYEES E
WHERE
E.DEPARTMENT_ID = D.DEPARTMENT_ID)
3. Write a query to find the departments
which do not have employees at all?
SELECT DEPARTMENT_ID,
DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE NOT EXISTS
(
SELECT 1
FROM
EMPLOYEES E
WHERE
E.DEPARTMENT_ID = D.DEPARTMENT_ID)
Nested
Sub Queries
A subquery
is nested when you are having a subquery in the where or having clause of
another subquery.
Get the result of all the students who are enrolled in the same course as the student with ROLLNO 12.
Get the result of all the students who are enrolled in the same course as the student with ROLLNO 12.
Select *
From result
where rollno in
(select rollno
from student
where courseid = (select
courseid
from student
where rollno
= 12));
The
innermost subquery will be executed first and then based on its result the next
subquery will be executed and based on that result the outer query will be
executed. The levels to which you can do the nesting is
implementation-dependent.
Scalar Sub Queries
Scalar subqueries allow you to treat the
output of a subquery as a column or even an expression within a SELECT
statement. It is a query that only selects one column or expression and returns just one row. If the
scalar subquery fails to return select any rows, Oracle will use a NULL value
for the output of the scalar subquery.
The following is an example of using the scalar
subquery to determine how many rows in the DEPT table contain an employee
corresponding to each row in the EMP table.
EXAMPLES-
·
Example 1 of
Scalar subquery
SELECT last_name,
job_id, salary
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees);
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees);
·
Example 2 of
Scalar subquery
SELECT d. deptno,
d.dname, (SELECT count(*) FROM emp e
WHERE e.deptno = d.deptno) AS "Num Dept"
FROM dept d;
WHERE e.deptno = d.deptno) AS "Num Dept"
FROM dept d;
DEPTNO
DNAME Num
Dept
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
Scalar Subqueries are mainly used for the following
tasks:
- Remove an Outer Join
- Aggregate from Multiple Tables
- Inserting into Tables, based on
Values from other Tables
Get involved
and leave your Comments in the Box Below. The more people get involved, the
more we all benefit.
So, leave your thoughts before you leave
the page.
Nice Blog, thaanks For Sharing this informative article.
ReplyDeleteOracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Workday HCM Online Training
I'd like to thank you for your time and effort that went into writing this essay.
ReplyDeleteI'm hoping for more of the same in the future from you. IAPPC, a leading Netsuite Partner in India for ERP installation and solution provider, provides end-to-end implementation services and support to meet the organization's demands.
Great post.
ReplyDeletestatvoo.com