?
Solved

how does a subquery work

Posted on 2006-10-19
3
Medium Priority
?
918 Views
Last Modified: 2012-08-13
I am searching for an article which tells me how a subquery works in T-SQL. I have a query like:

SELECT colA, colB
FROM TableA a
WHERE
      a.colA = (SELECT max(colA) FROM TableAB ab
                  WHERE a.colC = ab.colC)

how would this query work?
0
Comment
Question by:srinivas_vemla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 17769642
SELECT colA, colB
FROM TableA a
WHERE a.colA =
                     (
                          SELECT max(colA)
                          FROM TableAB ab
                          WHERE a.colC = ab.colC
                     )

This is not subquery.  This is Co-Related Subquery.  
The co-related subquery will be executed for each row in TableA.

Eg:  TableA has 10 rows.  So the subquery will be executed 10 times for each row in TableA.


itsvtk


0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 17769680
Please find below the description from Books online


Correlated Subqueries
==================
Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

This query finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book.

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
   (SELECT royaltyper
   FROM titleauthor
   WHERE titleauthor.au_ID = authors.au_id)

Here is the result set:

au_lname                                 au_fname            
---------------------------------------- --------------------
White                                    Johnson              
Green                                    Marjorie            
Carson                                   Cheryl              
Straight                                 Dean                
Locksley                                 Charlene            
Blotchet-Halls                           Reginald            
del Castillo                             Innes                
Panteley                                 Sylvia              
Ringer                                   Albert              

(9 row(s) affected)

Unlike most of the subqueries shown earlier, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable. It changes as Microsoft® SQL Server™ examines different rows of the authors table.

That is exactly how this query is evaluated: SQL Server considers each row of the authors table for inclusion in the results by substituting the value in each row into the inner query. For example, if SQL Server first examines the row for Cheryl Carson, the variable authors.au_id takes the value 238-95-7766, which SQL Server substitutes into the inner query.

USE pubs
SELECT royaltyper
FROM titleauthor
WHERE au_id = '238-95-7766'

The result is 100, so the outer query evaluates to:

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN (100)

Because this is true, the row for Cheryl Carson is included in the results. Go through the same procedure with the row for Abraham Bennet; you'll see that this row is not included in the results.

itsvtk
0
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 1000 total points
ID: 17769722
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question