Database Reference
In-Depth Information
statements can be combined into a single result set.
Example:
SELECT Customer_name, Customer _contact
FROM Customer
WHERE Customer _state IN ('UP','PB',HP')
UNION
SELECT Customer_name, Customer _contact
FROM Customer
WHERE Customer_name = 'Ajit';
7.3.4 Subqueries
Queries within a query are referred to as subqueries. A subquery is a SELECT statement
nested inside another query. It returns a value to the containing query for evaluation. The
query containing the subquery is referred to as the outer query. A subquery is often referred
to as an inner query, inner select, s u b select , or nested query. In a SELECT statement,
subqueries can appear in the SELECT, W H E R E , and HAVING clauses. The statement
having a subquery is called parent statement. The parent statement uses the result returned
by the subquery.
It's also possible to write a subquery within a subquery. This is called
nesting
. The outer-
most query is the first level, and each level of query below that is a nested level. You can go
several levels deep with nested queries. The breaking point is determined by how complex
the query is and how powerful a system you are using for the RDBMS.
Subqueries within the WHERE Clause
This nested query used to execute another SELECT statement, whose result will
then be evaluated with the remainder of the Boolean expression on that line.
Example: retrieve a list of first semester student who score more than 600 marks. Sol:
SELECT Name FROM Student
WHERE RollNo =
(SELECT RollNo FROM Student_result WHERE Semester =1 AND MarkObt > 600);
Example:
SELECT Customer_name, Customer_city FROM Customer
WHERE Customer_id IN (SELECT Customer_id
FROM Order
WHERE Order_no IN (SELECT Order_no FROM OrderItem
