Sub Queries

 
1.      Root query
The query is which is not dependet on any other query for a conditional  value Or Independent query

2.      Parent query
The query is which is dependent on any other query for a conditional value

Subquery

• ·       The query which provide conditional values to parent query
• ·       A sub queery in the where clause of a select statement is called a nested subquery.
• ·       A sub query in the from clause of a select statement is called a inline view query
• ·       A sub query can be part of a column in the select list
• ·       A sub query contain another subquery
• ·       Oracle don’t have noof sub query levels for the compact
• ·       With in the where clause 2 repated subquiers can be nested.
• ·       To make the statement is a readability, qualify of column in a sub query with table name (or) table alias.

·   

Syntax :

• The subquery (inner query) executes once before the main query (outer query) executes.
• The main query (outer query) use the subquery result.

Example

In this section, you will learn the requirements of using subqueries. We have the following two tables 'student' and 'marks' with common field 'StudentID'.

Now we want to write a query to identify all students who get better marks than that of the student who's StudentID is 'V002', but we do not know the marks of 'V002'.

- To solve the problem, we require two queries. One query returns the marks (stored in Total_marks field) of 'V002' and a second query identifies the students who get better marks than the result of the first query.

select * from marks where studentid='V002';  ---- V002   80

SELECT a.studentid, a.name, b.total_marks FROM student a, marks b
WHERE a.studentid = b.studentid
AND b.total_marks >80;

SELECT a.studentid, a.name, b.total_marks  FROM student a, marks b 

WHERE a.studentid = b.studentid AND b.total_marks >

(

SELECT total_marks FROM marks WHERE studentid =  'V002');

Type of Subqueries

1. Single row subquery : Returns zero or one row.
2. Multiple row subquery : Returns one or more rows.
3. Multiple column subqueries : Returns one or more columns.
4. Correlated subqueries : Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
5. Nested subqueries : Subqueries are placed within another subquery.