Using co-related queries in SQL

Tags:
Co-related queries
Query
SQL
SQL Query
How do you use co-related queries in SQL and why would they be useful? Is there any effect on performance when executing a co-related query?
ASKED: June 18, 2008  3:46 PM
UPDATED: June 19, 2008  2:34 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

here’s one example:
<pre>SELECT team
, player
, goals
FROM stats AS t
WHERE goals =
( SELECT MAX(goals) FROM stats WHERE team = t.team )</pre>gets the team and player who scored the most goals for his team

why would this be useful? bcoz it just is!!! ;o)

effect on performance? yes, usually a join to a derived table is faster
<pre>SELECT t.team
, t.player
, t.goals
FROM stats AS t
INNER
JOIN ( SELECT team
, MAX(goals) AS maxgoals
FROM stats
GROUP BY team ) AS m
ON m.team = t.team
AND m.maxgoals = t.goals</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following