My Question is what is the best way to learn subqueries? And why are they referred to as dynamic queries? Thanks
"Subqueries" and "Dynamic" queries are not necessarily interchangeable.
Can you provide more info? Maybe a code sample?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 9, 2019 at 11:43 am
Sub queries come in several stripes. Correlated sub-queries (which I shy away from)
SELECT
(SELECT b.cola FROM tableb AS b
WHERE b.ID = a.id)
FROM tablea AS a
WHERE a.otherid = 42;
Or, as a table defined sub-query:
SELECT a.cola,
b.colb
FROM tablea AS a
JOIN (SELECT c.colb, c.id FROM tableb AS c
WHERE something someting) AS b
ON a.id = b.id
WHERE...
You can also see sub-selects in the WHERE clause. You'll also see them in CROSS APPLY and COMMON TABLE EXPRESSIONS. You can look up examples of all of the above.
How do you use them? I'd suggest looking at lots of examples to understand why people might define a SELECT statement separate from the straight-forward one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2019 at 2:24 pm
You can read about them here: https://www.sqlservercentral.com/steps/stairway-to-t-sql-beyond-the-basics-level-2-writing-subqueries
October 11, 2019 at 2:04 pm
All good replies above. I have never heard of subqueries referred to as dynamic queries.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply