SQL – Subqueries and correlated queries

A subquery involves nesting however when the column from an relation (table) in an outer query is also used in an enveloped query then the queries become correlated.

What can be achieved by a correlated query can sometimes not be achievable by simply using joins or nested queries.

For instance: in the Supplier-Parts database from CJ Date’s Intro to DBMS, the following is an example of a correlated query:

Find all the supplier name for suppliers that supply part "P2":

select SNAME from S

where ‘P2′ IN

(select P# from SP

where S.S# = SP.S#)

 Note: The outer query is executed once every time the inner query returns a result.

Leave a Reply

Your email address will not be published. Required fields are marked *

     

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">