{"id":421,"date":"2008-02-07T08:01:46","date_gmt":"2008-02-07T08:01:46","guid":{"rendered":"\/blogs\/conor\/post\/EXISTS-Subqueries-SELECT-1-vs-SELECT.aspx"},"modified":"2008-02-07T08:01:46","modified_gmt":"2008-02-07T08:01:46","slug":"exists-subqueries-select-1-vs-select","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/","title":{"rendered":"EXISTS Subqueries: SELECT 1 vs. SELECT"},"content":{"rendered":"<p>I received a comment asking about this, so I&#8217;ll write a short note on the subject.<\/p>\n<p>The QP will take and expand all *&#8217;s early in the pipeline and bind them to objects (in this case, the list of columns).&nbsp; It will then remove unneeded columns due to the nature of the query.&nbsp; <\/p>\n<p>So for a simple EXISTS subquery like this:<\/p>\n<pre><span style=\"color: Black; background-color: transparent; font-family: Courier New; font-size: 11px;\"><span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">SELECT<\/span> col1 <span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">FROM<\/span> MyTable <span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">WHERE<\/span> <span style=\"color: Silver; background-color: transparent; font-family: Courier New; font-size: 11px;\">EXISTS<\/span> (<span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">SELECT<\/span> * <span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">FROM<\/span> Table2 <span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">WHERE<\/span> MyTable.col1=Table2.col2)<\/span><\/pre>\n<p>The * will be expanded to some potentially big column list and then it will be determined that the semantics of the EXISTS does not require any of those columns, so basically all of them can be removed.<\/p>\n<p>&#8220;SELECT 1&#8221; will avoid having to examine any unneeded metadata for that table during query compilation.<\/p>\n<p>However, at runtime the two forms of the query will be identical and will have identical runtimes.<\/p>\n<p>As such, I typically use SELECT 1.&nbsp; In my blog post I didn&#8217;t.<\/p>\n<p>Conor<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I received a comment asking about this, so I&#8217;ll write a short note on the subject. The QP will take and expand all *&#8217;s early in the pipeline and bind them to objects (in this case, the list of columns).&nbsp; It will then remove unneeded columns due to the nature of the query.&nbsp; So for [&hellip;]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-421","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>EXISTS Subqueries: SELECT 1 vs. SELECT - Conor Cunningham<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"EXISTS Subqueries: SELECT 1 vs. SELECT - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"I received a comment asking about this, so I&#8217;ll write a short note on the subject. The QP will take and expand all *&#8217;s early in the pipeline and bind them to objects (in this case, the list of columns).&nbsp; It will then remove unneeded columns due to the nature of the query.&nbsp; So for [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-02-07T08:01:46+00:00\" \/>\n<meta name=\"author\" content=\"Conor Cunningham\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Conor Cunningham\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/\",\"name\":\"EXISTS Subqueries: SELECT 1 vs. SELECT - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-02-07T08:01:46+00:00\",\"dateModified\":\"2008-02-07T08:01:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"EXISTS Subqueries: SELECT 1 vs. SELECT\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\",\"name\":\"Conor Cunningham\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\",\"name\":\"Conor Cunningham\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"caption\":\"Conor Cunningham\"},\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"EXISTS Subqueries: SELECT 1 vs. SELECT - Conor Cunningham","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/","og_locale":"en_US","og_type":"article","og_title":"EXISTS Subqueries: SELECT 1 vs. SELECT - Conor Cunningham","og_description":"I received a comment asking about this, so I&#8217;ll write a short note on the subject. The QP will take and expand all *&#8217;s early in the pipeline and bind them to objects (in this case, the list of columns).&nbsp; It will then remove unneeded columns due to the nature of the query.&nbsp; So for [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/","og_site_name":"Conor Cunningham","article_published_time":"2008-02-07T08:01:46+00:00","author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/","name":"EXISTS Subqueries: SELECT 1 vs. SELECT - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-02-07T08:01:46+00:00","dateModified":"2008-02-07T08:01:46+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/exists-subqueries-select-1-vs-select\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"EXISTS Subqueries: SELECT 1 vs. SELECT"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/","name":"Conor Cunningham","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3","name":"Conor Cunningham","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","caption":"Conor Cunningham"},"url":"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/421","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/comments?post=421"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/421\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=421"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=421"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=421"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}