SUBQUERIES (or) INNER QUERY

Definition:

Sub query is defined as a query within a query. That is one select query contains another select query. The inner query will be executed first. The outer query (or) main query and inner query are connected by operators.

The sub query can be placed in a number of SQL clauses, including:

Ø  The SELECT clause (or) Scalar Subquery

Ø  The FROM clause (or) In-Line view

Ø  The WHERE clause

Ø  The HAVING clause

Types:

Ø  Single Row Sub-query

Ø  Multiple Row Sub-query

Single Row Sub-query / Multiple Row Sub-query
Relational operators should be used for condition clause.
Returns only one row. / ANY, ALL, IN operators should be used for condition clause.
Returns more than one row.

The multiple row sub query outer and inner queries are connected by one of the following operators. They are

i)  ANY

ii)  ALL

iii)  IN

iv)  NOT IN

General Syntax for Sub-query:

outer query operator(inner query)

Where,

Outer, inner queries - valid sql query

Operators - any valid operator such

ANY, ALL, IN, NOT IN

SINGLE ROW SUB QUERY:

Example 01:

select * from emp;

Output:

EMP_ID / FIRST_NAME / LAST_NAME / DESIGNATION / SALARY / DOJ / LOAN_AMT
1 / Al / Basith / CEO / 25000 / 12-JAN-92 / 100000
2 / Siva / Guru / Manager / 20000 / 12-MAY-95 / -
3 / Kiruba / Karan / Ass-Mngr / 15000 / 21-DEC-97 / -
4 / Steven / Raj / Client / 30000 / 12-MAR-90 / -
105 / Kiruba / Karan / Ass-Mngr / 17000 / 29-AUG-13 / 5000

select first_name, salary from emp where salary < (select max(salary) from emp);

Output:

FIRST_NAME / SALARY
Al / 25000
Siva / 20000
Kiruba / 15000
Kiruba / 17000

Example 02:

select first_name, salary from emp where salary= (select max (salary) from emp) or salary=(select min(salary) from emp);

Output:

FIRST_NAME / SALARY
Kiruba / 15000
Steven / 30000

Example 03:

select * from gender;

Output:

G
M
M
M
F
F
F
M

select (select count(g) as M from gender where g='F' ), (select count(*) as F from gender where g='M'),(select count(*) as T from gender) from dual;

Output:

F / M / T
3 / 4 / 7

MULTIPLE ROW FUNCTION:

Example 01:

select * from emp where salary > any (15000,25000,17000);

Output:

EMP_ID / FIRST_NAME / LAST_NAME / DESIGNATION / SALARY / DOJ / LOAN_AMT
1 / Al / Basith / CEO / 25000 / 12-JAN-92 / 100000
2 / Siva / Guru / Manager / 20000 / 12-MAY-95 / -
4 / Steven / Raj / Client / 30000 / 12-MAR-90 / -
105 / Kiruba / Karan / Ass-Mngr / 17000 / 29-AUG-13 / 5000

Example 02:

select * from emp where salary < any (15000,25000,17000);

Output:

EMP_ID / FIRST_NAME / LAST_NAME / DESIGNATION / SALARY / DOJ / LOAN_AMT
2 / Siva / Guru / Manager / 20000 / 12-MAY-95 / -
3 / Kiruba / Karan / Ass-Mngr / 15000 / 21-DEC-97 / -
105 / Kiruba / Karan / Ass-Mngr / 17000 / 29-AUG-13 / 5000

Example 03:

select * from emp where salary = any (15000,25000,17000);

Output:

EMP_ID / FIRST_NAME / LAST_NAME / DESIGNATION / SALARY / DOJ / LOAN_AMT
1 / Al / Basith / CEO / 25000 / 12-JAN-92 / 100000
3 / Kiruba / Karan / Ass-Mngr / 15000 / 21-DEC-97 / -
105 / Kiruba / Karan / Ass-Mngr / 17000 / 29-AUG-13 / 5000

Example 04:

select * from emp where salary>all(15000,25000,17000);

Output:

EMP_ID / FIRST_NAME / LAST_NAME / DESIGNATION / SALARY / DOJ / LOAN_AMT
4 / Steven / Raj / Client / 30000 / 12-MAR-90 / -

Example 05:

select * from emp where salary in(select salary from emp where salary> 17000);

Output:

EMP_ID / FIRST_NAME / LAST_NAME / DESIGNATION / SALARY / DOJ / LOAN_AMT
1 / Al / Basith / CEO / 25000 / 12-JAN-92 / 100000
2 / Siva / Guru / Manager / 20000 / 12-MAY-95 / -
4 / Steven / Raj / Client / 30000 / 12-MAR-90 / -

Example 06:

select * from emp where salary not in(select salary from emp where salary> 17000);

Output:

EMP_ID / FIRST_NAME / LAST_NAME / DESIGNATION / SALARY / DOJ / LOAN_AMT
3 / Kiruba / Karan / Ass-Mngr / 15000 / 21-DEC-97 / -
105 / Kiruba / Karan / Ass-Mngr / 17000 / 29-AUG-13 / 5000

Since the salary amount is repeated multiple times the multiple row function is used.

Thus the above query using the ANY operator returns the least salary amount.

If the greater salary amount is required ALL operator returns the greater salary amount.

Same MRF (multiple row function) uses the IN,NOT IN operators as per the requirements.

INTERVIEW QUESTIONS:

01.  What is Inline Function?

In-line function uses the FROM clause and it has an sub query inside using SELECT clause and has an condition in it.

02.What is Scalar subquery?

Scalar subquery is meant for using the SELECT clause and the other name for SELECT clause is known as Scalar subquery.