{"id":429,"date":"2024-06-28T09:33:03","date_gmt":"2024-06-28T09:33:03","guid":{"rendered":"https:\/\/datadandies.nl\/?p=429"},"modified":"2024-06-28T09:33:03","modified_gmt":"2024-06-28T09:33:03","slug":"correlated-vs-uncorrelated-subqueries","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2024\/06\/28\/correlated-vs-uncorrelated-subqueries\/","title":{"rendered":"Correlated vs uncorrelated subqueries"},"content":{"rendered":"\n

A subquery is a query that is used in another query.<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> (<\/span>SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> tableA)<\/span><\/span>\n<\/span>\n--Outer query: SELECT * FROM <\/span><\/span>\n--Sub query: (SELECT * FROM tableA) <\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
Another example of subqueries can be as simple as the example below.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span><\/span>\n\t\t(<\/span>SELECT<\/span> <\/span>COUNT<\/span>(<\/span>*<\/span>) <\/span>FROM<\/span> tableA), <\/span>-- Subquery 1<\/span><\/span>\n\t\t(<\/span>SELECT<\/span> <\/span>COUNT<\/span>(<\/span>*<\/span>) <\/span>FROM<\/span> tableB)  <\/span>-- Subquery 2<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
A correlated subquery is a subquery where a reference is made to the outer query.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> tableB<\/span><\/span>\n\t<\/span>WHERE<\/span> <\/span>EXISTS<\/span> (<\/span>SELECT<\/span> <\/span>1<\/span> <\/span>FROM<\/span> tableA <\/span>WHERE<\/span> tableB.column1 <\/span>=<\/span> tableA.column2) <\/span><\/span>\n-- A column from the outer query with tableB is used in the subquery<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
An uncorrelated subquery is a subquery where no reference is made to the outer query.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span>*<\/span> <\/span>FROM<\/span> tableB<\/span><\/span>\nWHERE<\/span> tableB.column1 <\/span>=<\/span> (<\/span>SELECT<\/span> <\/span>MAX<\/span>(tableA.column2) <\/span>FROM<\/span> tableA)<\/span><\/span>\n-- No columns from the outer query with tableA are used in the subquery<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
For the record, in Snowflake you can get away with not giving your subquery an alias, while SQL Server will give you a hard time about it. <\/p>\n\n\n\n
For the recordest, I am not a big fan of subqueries, I find that Common Table Expressions (CTEs) make queries easier to read in most cases. Also, CTEs are reusable. However, you do find these subqueries a lot in the wild so I find it nice to at least know what they are.<\/p>\n","protected":false},"excerpt":{"rendered":"
A subquery is a query that is used in another query. Another example of subqueries can be as simple as the example below. A correlated subquery is a subquery where a reference is made to the outer query. An uncorrelated subquery is a subquery where no reference is made to the outer query. For the…<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[4],"class_list":["post-429","post","type-post","status-publish","format-standard","hentry","category-blog","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/429","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=429"}],"version-history":[{"count":1,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/429\/revisions"}],"predecessor-version":[{"id":430,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/429\/revisions\/430"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=429"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=429"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}