{"id":391,"date":"2008-06-02T19:32:50","date_gmt":"2008-06-02T19:32:50","guid":{"rendered":"\/blogs\/conor\/post\/Outer-joins-and-ON-clauses-vs-WHERE-clauses.aspx"},"modified":"2008-06-02T19:32:50","modified_gmt":"2008-06-02T19:32:50","slug":"outer-joins-and-on-clauses-vs-where-clauses","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/","title":{"rendered":"Outer joins and ON clauses vs. WHERE clauses"},"content":{"rendered":"

(I am still around – I’ve just been busy with a few personal projects of late – keep asking questions if you have them).<\/p>\n

I received a question from a reader named Andy that was a follow-up to my questions on OUTER JOINs and WHERE clauses and ON clauses.<\/p>\n

Upon re-reading my previous entries, I determined that I got distracted talking about the old-style join syntax instead of giving a good explanation about ON vs. WHERE _semantics_ for outer joins.<\/p>\n

select * from cc1 left outer join cc2 on (cc1.col1=cc2.col1)<\/p>\n

drop<\/span> table<\/span> cc1\r\ncreate<\/span> table<\/span> cc1(col1 int<\/span>, col2 int<\/span>)\r\ninsert<\/span> into<\/span> cc1(col1, col2) values<\/span> (1, 2)\r\ninsert<\/span> into<\/span> cc1(col1, col2) values<\/span> (2, 3)\r\n\r\ncreate<\/span> table<\/span> cc2(col1 int<\/span>, col2 int<\/span>)\r\ninsert<\/span> into<\/span> cc2(col1, col2) values<\/span> (1, 2)\r\n\r\nselect<\/span> * from<\/span> cc1 left<\/span> outer<\/span> join<\/span> cc2 on<\/span> (cc1.col1=cc2.col1)

col1        col2        col1        col2
----------- ----------- ----------- -----------
1           2           1           2
2           3           NULL        NULL

(2 row(s) affected)


select<\/span> * from<\/span> cc1 left<\/span> outer<\/span> join<\/span> cc2 ON<\/span> (1=1) WHERE<\/span> (cc1.col1=cc2.col1)

col1        col2        col1        col2
----------- ----------- ----------- -----------
1           2           1           2

(1 row(s) affected)

<\/span><\/pre>\n
The question boils down to “why are these two queries returning different results?”  This is a very good question.<\/span><\/font><\/p>\n
<\/span><\/font><\/p>\n
I’ll explain the what, then I’ll try to explain the why\/how:<\/span><\/font><\/p>\n
<\/span><\/font><\/p>\n
In the first query, the ON clause has the predicate.  This logically ties the predicate to the join.  Since OUTER JOINS can return rows that do not match, the predicate is used to determine what “doesn’t match”.<\/span><\/font><\/p>\n
<\/span><\/font><\/p>\n
In the second case, the join part actually tells the QP to do a full cross product.  The WHERE clause is actually not bound to the OUTER join semantics at all. This is why they return different results.  
<\/span><\/font><\/p>\n
Now let’s talk “why\/how”.  The QP represents this second query as a tree with a filter above the join.  The QP has lots of smarts in it.  One of the thing it can determine is that the filter condition (cc1.col1=cc2.col2) actually prevents NULLs from being returned since NULL = anything is UNKNOWN in three-value logic (and therefore not TRUE and therefore not returned from the WHERE clause).  Since all NULL values from the non-outer side of the join are removed, this is logically equivalent to running an inner join because all of the extra rows for non-matching rows are actually removed.<\/font><\/p>\n
select<\/span> * from<\/span> cc1 INNER<\/b> join<\/span> cc2 ON<\/span> (1=1) WHERE<\/span> (cc1.col1=cc2.col1)<\/span><\/pre>\n
<\/span><\/font><\/p>\n
If you look at the query plans, you will see that the second query is actually running an inner join because of this recognition in the QP that you don’t need an outer join.  (The QP can more freely reorder inner joins, so it prefers to convert outer to inner joins where possible).<\/p>\n
I hope that gives a better explanation as to why SQL Server returns different results for this query!<\/p>\n
Happy Querying!<\/p>\n
Conor<\/p>\n

<\/span><\/font><\/p>\n
 <\/span><\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
(I am still around – I’ve just been busy with a few personal projects of late – keep asking questions if you have them). I received a question from a reader named Andy that was a follow-up to my questions on OUTER JOINs and WHERE clauses and ON clauses. Upon re-reading my previous entries, I […]<\/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-391","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nOuter joins and ON clauses vs. WHERE clauses - 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\/outer-joins-and-on-clauses-vs-where-clauses\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Outer joins and ON clauses vs. WHERE clauses - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"(I am still around – I’ve just been busy with a few personal projects of late – keep asking questions if you have them). I received a question from a reader named Andy that was a follow-up to my questions on OUTER JOINs and WHERE clauses and ON clauses. Upon re-reading my previous entries, I […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-06-02T19:32:50+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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/\",\"name\":\"Outer joins and ON clauses vs. WHERE clauses - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-06-02T19:32:50+00:00\",\"dateModified\":\"2008-06-02T19:32:50+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Outer joins and ON clauses vs. WHERE clauses\"}]},{\"@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":"Outer joins and ON clauses vs. WHERE clauses - 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\/outer-joins-and-on-clauses-vs-where-clauses\/","og_locale":"en_US","og_type":"article","og_title":"Outer joins and ON clauses vs. WHERE clauses - Conor Cunningham","og_description":"(I am still around – I’ve just been busy with a few personal projects of late – keep asking questions if you have them). I received a question from a reader named Andy that was a follow-up to my questions on OUTER JOINs and WHERE clauses and ON clauses. Upon re-reading my previous entries, I […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/","og_site_name":"Conor Cunningham","article_published_time":"2008-06-02T19:32:50+00:00","author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/","name":"Outer joins and ON clauses vs. WHERE clauses - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-06-02T19:32:50+00:00","dateModified":"2008-06-02T19:32:50+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/outer-joins-and-on-clauses-vs-where-clauses\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"Outer joins and ON clauses vs. WHERE clauses"}]},{"@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\/391","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=391"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/391\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=391"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=391"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=391"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}