[go: up one dir, main page]
Scribd
Upload
27 views2 pages

Subqueries AD3391 Notes

The document explains subqueries and correlated subqueries in SQL, highlighting their definitions, types, and differences. Subqueries are executed once and can be used in various clauses, while correlated subqueries depend on the outer query and are executed for each row. Both types enhance query modularity and flexibility for complex operations.

Uploaded by

hemavasanth69
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
27 views2 pages

Subqueries AD3391 Notes

The document explains subqueries and correlated subqueries in SQL, highlighting their definitions, types, and differences. Subqueries are executed once and can be used in various clauses, while correlated subqueries depend on the outer query and are executed for each row. Both types enhance query modularity and flexibility for complex operations.

Uploaded by

hemavasanth69
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Subqueries and Correlated Subqueries -

AD 3391 Notes
1. Introduction
SQL allows writing queries inside another query; these are called subqueries. They are used
to perform intermediate steps or comparisons.

2. Subqueries (Nested Queries)


A subquery is a query written inside another SQL query. It is executed only once, and the
result is passed to the outer query. Subqueries can be used with SELECT, FROM, or WHERE
clauses.

Types of Subqueries:

 - Single-row subquery – returns one row


 - Multi-row subquery – returns multiple rows
 - Scalar subquery – returns a single value

Example:

SELECT name
FROM students
WHERE sid IN (
SELECT sid
FROM enrolls
WHERE grade = 'A'
);

3. Correlated Subqueries
A correlated subquery uses values from the outer query. It is executed once for every row of
the outer query and cannot be run independently.

Example:

SELECT name
FROM students s
WHERE EXISTS (
SELECT *
FROM enrolls e
WHERE e.sid = s.sid AND e.grade = 'A'
);

4. Differences Between Subquery and Correlated Subquery


Subquery Correlated Subquery
Runs once Runs for each row in outer query
No reference to outer query References outer query
Faster in most cases Slower but useful in comparisons
Can run independently Cannot run independently

5. Uses of Subqueries
 - Filtering rows
 - Comparing aggregates
 - Performing joins in a simpler way
 - Replacing complex joins

6. Conclusion
Subqueries and correlated subqueries are powerful SQL tools. They make queries modular,
readable, and flexible for complex operations.

You might also like