{"id":1401,"date":"2012-12-17T10:00:27","date_gmt":"2012-12-17T15:00:27","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1401"},"modified":"2014-11-13T13:51:03","modified_gmt":"2014-11-13T18:51:03","slug":"a-join-a-day-except","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/","title":{"rendered":"A Join A Day – Except"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nThis is the seventeenth post in my A Join A Day<\/a> series about SQL Server Joins. Make sure to let me know how I am doing or ask your burning join related questions by leaving a comment below.\n<\/p>\n

\nLike the INTERSECT<\/span> key word, the EXCEPT<\/span> key word was added to the T-SQL language with the SQL Server 2005 release. It does not look like a join at first, but gets handled by SQL Server like one. Also – like INTERSECT<\/span> – EXCEPT<\/span> comes directly from the mathematical set theory (pdf)<\/a>.\n<\/p>\n

EXCEPT Example<\/h3>\n

\nLet's look at the same example sets again that we used during the INTERSEC<\/span> post<\/a> yesterday: : {A, B, M, T, Y, Z} and {G, K, L, M, O, Y}\n<\/p>\n

\n\"two<\/a>\n<\/p>\n

\nThe EXCEPT<\/span> key word represents the difference in set theory. The difference of two sets is defined as the set of the elements that are in the first set but not in the second set. It is denoted with the \\<\/span> sign:\n<\/p>\n

\n\"difference<\/a>\n<\/p>\n

\n {A, B, M, T, Y, Z} \\ {G, K, L, M, O, Y} = {A, B, T, Z}<\/span>\n<\/p>\n

\nThe EXCEPT<\/span> keyword therefore returns all rows from the first query expression that are not part of the second query expression. Let's look at the TblA \u2013 TblB<\/span> example again. The tables look like this:\n<\/p>\n

\n\"setup<\/a>\n<\/p>\n

\nLet's run the following query:\n<\/p>\n

\n[sql]\nSELECT TblA_Val FROM dbo.TblA
\nEXCEPT
\nSELECT TblB_Val FROM dbo.TblB
\n[\/sql]\n<\/div>\n

\nThis query results in only a single row:\n<\/p>\n

\n\"except<\/a>\n<\/p>\n

\nThe value 1 is the only one that is contained in TblA<\/span> but not contained in TblB<\/span>.\n<\/p>\n

\nThis looks exactly like the result we got back from the NOT EXISTS()<\/span> query in the Left Anti Semi Join<\/a> post. But again, there is one important difference. To show that difference we need to add two more rows to TblA<\/span>:\n<\/p>\n

\n[sql]\nINSERT INTO dbo.TblA(TblA_Val, TblA_Desc)
\nVALUES (9,'A-9 (1st'), (9, 'A-9 (2nd)');
\n[\/sql]\n<\/div>\n

\nNow the result of above query looks like this:\n<\/p>\n

\n\"except<\/a>\n<\/p>\n

\nNow let's recall the NOT EXISTS()<\/span> query, modified to also only return the TblA_Val<\/span> column:\n<\/p>\n

\n[sql]\nSELECT TblA_Val
\nFROM dbo.TblA AS a
\nWHERE NOT EXISTS ( SELECT 1
\n FROM dbo.TblB AS b
\n WHERE a.TblA_Val = b.TblB_Val );
\n[\/sql]\n<\/div>\n

\nThe result of this query looks like this:\n<\/p>\n

\n\"not<\/a>\n<\/p>\n

\nThe difference between EXCEPT<\/span> and NOT EXISTS()<\/span> is the same as the difference between INTERSECT<\/span> and EXISTS()<\/span>: The EXCEPT<\/span> keyword causes duplicate rows to be eliminated from the final result. This is intentional as the action is borrowed from set theory and a set cannot contain the same element more than once.\n<\/p>\n

\nIf the column list contains the primary key (or another unique column) from TblA<\/span>, the result of an EXCEPT<\/span> query and a NOT EXISTS()<\/span> query are the same.\n<\/p>\n

If you are following along with the examples use the following statement to clean up the additional rows.\n<\/p>\n

\n[sql]\nDELETE dbo.TblA WHERE TblA_Val = 9;
\n[\/sql]\n<\/div>\n<\/p>\n

EXECPT Syntax<\/h3>\n

\nLike INTERSECT<\/span>, the use of the EXCEPT<\/span> key word tends to lead to cleaner and easier to maintain queries.\n<\/p>\n

\nThe syntax is similar to the UNION<\/span> and the INTERSECT<\/span> statements. You can connect any two queries with the EXCEPT<\/span> statement. The only restriction is here too that the column count and data types must match. Again, implicit conversions are allowed.\n<\/p>\n

\nThe full EXCEPT<\/span> syntax you can find here: http:\/\/msdn.microsoft.com\/en-us\/library\/ms188055(v=sql.105).aspx<\/a>\n<\/p>\n

EXCEPT Operator<\/h3>\n

\nAs you might have expected by now, the operator of choice for EXCEPT<\/span> queries is the Left Anti Semi Join:\n<\/p>\n

\n\"except<\/a>\n<\/p>\n

\nA Stream Aggregate operator is added here too, to remove the duplicate rows.\n<\/p>\n

Summary<\/h3>\n

\nEXCEPT<\/span> is a T-SQL statement that was introduced in SQL Server 2005. It returns the set-theoretical difference of two query results. Rows from the left are returned as long as they are not part of the right also. Because the result is meant to be a set of rows, duplicate rows are automatically removed.\n<\/p>\n

A Join A Day<\/h3>\n

\nThis post is part of my December 2012 \"A Join A Day\" blog post series. You can find the table of contents with all posts published so far in the introductory post: A Join A Day \u2013 Introduction<\/a>. Check back there frequently throughout the month.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[28,29,5,27,23],"tags":[],"class_list":["post-1401","post","type-post","status-publish","format-standard","hentry","category-a-join-a-day","category-fundamentals","category-general","category-series","category-t-sql-statements"],"acf":[],"yoast_head":"\nA Join A Day - Except - sqlity.net<\/title>\n<meta name=\"description\" content=\"The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A Join A Day - Except - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2012-12-17T15:00:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:51:03+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/two-sets.png\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"A Join A Day – Except\",\"datePublished\":\"2012-12-17T15:00:27+00:00\",\"dateModified\":\"2014-11-13T18:51:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/\"},\"wordCount\":764,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/two-sets.png\",\"articleSection\":[\"A Join A Day\",\"Fundamentals\",\"General\",\"Series\",\"T-SQL Statements\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/\",\"name\":\"A Join A Day - Except - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/two-sets.png\",\"datePublished\":\"2012-12-17T15:00:27+00:00\",\"dateModified\":\"2014-11-13T18:51:03+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/#primaryimage\",\"url\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/two-sets.png\",\"contentUrl\":\"http:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2012\\\/12\\\/two-sets.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1401\\\/a-join-a-day-except\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A Join A Day – Except\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"A Join A Day - Except - sqlity.net","description":"The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.","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:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/","og_locale":"en_US","og_type":"article","og_title":"A Join A Day - Except - sqlity.net","og_description":"The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.","og_url":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2012-12-17T15:00:27+00:00","article_modified_time":"2014-11-13T18:51:03+00:00","og_image":[{"url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/two-sets.png","type":"","width":"","height":""}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"A Join A Day – Except","datePublished":"2012-12-17T15:00:27+00:00","dateModified":"2014-11-13T18:51:03+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/"},"wordCount":764,"commentCount":0,"image":{"@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/two-sets.png","articleSection":["A Join A Day","Fundamentals","General","Series","T-SQL Statements"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/","url":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/","name":"A Join A Day - Except - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/#primaryimage"},"thumbnailUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/two-sets.png","datePublished":"2012-12-17T15:00:27+00:00","dateModified":"2014-11-13T18:51:03+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"The EXCEPT keyword can be used to calculate the set theoretical difference of two sets of rows. You can use EXCEPT every time you want to return rows from one table that are not in another table. Under the covers SQL Server uses a Left Anti Semi Join too fulfill an EXCEPT requests.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/#primaryimage","url":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/two-sets.png","contentUrl":"http:\/\/sqlity.net\/wp-content\/uploads\/2012\/12\/two-sets.png"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1401\/a-join-a-day-except\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"A Join A Day – Except"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-mB","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1401"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1401\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}