0Writing the Query in one more Query is called Sub Query.
In Oracle SQl Sub Queries are Diveded into two categories
- Simple Sub Queries
- Cor-related Sub Queries
The difference between Simple Sub Queries and Cor-related Sub Queries is in case of Simple Sub Queries the outer side Query Expression can compare with inner side query only once. In the case of Cor-related Sub Queries the outward side query expression can compare with internal side query number of times based on the condition which we have specified.
Simple Sub Queries are the following categories:
- Sub Query Comparison Test
- Sub Query Membership Test
- Sub Query Quantified Comparison Test
- Existance Test
Sub Query Comparison Test: In this case, the inner side query can return only one record while comparing we can use any relational operator
Sub Query Membership Test: In this case, the inner side query can return more than one record while comparing we can use 'in' operator and the outer side query expression can compare with any one of the values which are produced by the Sub Query.
Sub Query Quantified Comparison Test: In this case, the inner side query can return more than one record, and the outer side query expression can compare with all the values which are produced by the subquery while comparing we can use operators >any, <any, >all,
Existence Test: To check the existence of record which are satisfying primary key and foreign key relationship between two columns which are existing in the same table.
