SQL SUBQUERY AND CO-RELATED SUBQUERY IN ORACLE #Diksha Oracle Training Centre

 SQL SUBQUERY AND CO-RELATED SUBQUERY IN ORACLE.

Hello Everyone,

Today I will give you the overview of Sub query and CO-RELATED Sub Query. Before starting this topic I suggest you to please go through my previous blogs related with SQL joins and types of Join, Classification of SQL and various commands in SQL.

Before knowing SUBQUERY and CO-RELATED SUBQUERY first need to understand query.

Query :

 A query is basically used to request data or information from database tables or combination of tables to display the result .  In another word Data will be generated as a result used by SQL (Structure Query Language). In SQL statement we use select clause to fetch the data, from clause to pull data from the table and where clause to filter the result/output.

For Example : If I need student_name from student table who lives in Delhi we need to query the data from student table.  To get the result we use the below query :

Select student_name from student where location=’Delhi’;

Where— to filter the result.

Sub Query

 Query within Query is known as Sub query . SQL sub query are an alternate way of returning data from multiple tables.

Comparison Operators used in Sub queries are

=, <, >, >=, <= etc                 

Sub queries uses below Keyword in SQL:

IN  - Subqueries that used IN keyword can return a list of zero or more in results.

Example :

Syntax:

Select Name From Student where id in ( select id from student_details where location=’Delhi’);

Here result set can be zero or multiple depends on inner query result set.

ANY  / ALL  operator :

The keywords ANY and ALL modify a comparison operator that used a sub query.

When any is used operator with <, >, or = with a sub query, it returns results when any value retrieved in the sub query matches the value in the where or having clause of the outer statement.

When ALL is used with < or > operator in a sub query, it returns results when all values retrieved in the sub query match the value in the where or having clause of the outer statement.

Using the > comparison operator as an example:

> ALL means greater than every value, or greater than the maximum value. For example :  > all (5, 6, 7) it means greater than 7.

> ANY means greater than at least one value, or greater than the minimum value. Therefore: > any (5, 6, 7) means greater than 5.

Note : If you use a sub query with all and a comparison operator does not return any values, the entire query fails.

Syntax of ALL and Any, Please understand as it’s bit logical and tricky.

Note: Here I am using only 3 tables i.e. Student, School and Student details to use the sub query with Operators.

 

Example:

1.  select sum(fee_deposit)  , ID from student_details

   where ID IN (select ID from student where ID IN(1,2,3,4) )

   group by ID;

  

2.  select name  from student  where id > all

(select  school.id

   from student_details, school

     where school.id = student_details.id

             and school.location = 'Delhi') ;

 

Output of above Sub query is as below in Oracle Compiler.

 

       

3.    select * from student

       where id > all

 (select id from school where location=’Delhi’);

 Output of above query is as below in Oracle Compiler.

Through Sub query we can also do DML operation.

Example:

INSERT INTO STUDENT(ID,NAME)

 select id , first_name||' '||last_name from school where LOCATION='DELHI';

Here we use concatenate operator i.e || to join first name and last name in 1 record to insert the name value in Student table.

Output of above query is as below:

Note: I have given basic example of IN, ANY and ALL keywords used in SUBQUERY also we can use different operator which I explained you can be used in SUBQUERY. Please try to create some more tables and try to write all type of Sub queries and if any issue please comment in blog.

 

Correlated Sub query:

When both the inner query and outer query are interdependent is known as Correlated Sub query. In correlated sub query the inner query depends on the outer query before it processed. In other word we can say every row processed by the inner query, the outer query processed as well.

 

Simple Example by using Correlated Sub query:

Example:

Select a.name from student a

Where a.id =(select b.id from student_details b

Where a.id=b.id);

Output of above Query is as below:

 

Non-Corelated Subquery

 

If a sub query is not dependent in the outer query is known as Non-Correlated Sub query.

 

Note: Please create some logical tables and put all the records in table by using DML and play the data against requirements. Take reference of all the queries from this blog and try to  write as much query as you can and if any doubts let me know.

Please try to make some design and create multiple tables with logical data by using DDL and DML and use query , Sub query , Co-related sub query  and Joins as much as you can .For any issues let me know . Joins I already covered in my previous blog. In next blog I will come up with different SQL topic. Try to write some complex queries with multiple tables by taking this reference

Thanks.