{"id":377,"date":"2019-05-26T04:01:23","date_gmt":"2019-05-26T12:01:23","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=377"},"modified":"2020-06-29T22:31:48","modified_gmt":"2020-06-30T06:31:48","slug":"db2-except","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/","title":{"rendered":"Db2 EXCEPT"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to use the Db2 <code>EXCEPT<\/code> to return the result of the first subselect minus any matching rows from the result set of the second subselect.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Db2 EXCEPT Operator Overview<\/h2>\n\n\n\n<p>The Db2 <code>EXCEPT<\/code> combines two or more result sets of subselects. It returns the result set of the first subselect minus any matching rows of the result of the second subselect.<\/p>\n\n\n\n<p>Here is the syntax of the Db2 <code>EXCEPT<\/code> operator:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">subselect_1\nEXCEPT\nsubselect_2\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The columns and expression in the select list of the subselects must follow these rules:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The number and order of columns or expressions must be the same in both subselects.<\/li><li>The data types of the corresponding columns or expressions must be the same or compatible.<\/li><\/ul>\n\n\n\n<p>The following picture illustrates the <code>EXCEPT<\/code> operation of the two result sets T1 (A, B, C) and T2 (B, C, D):<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"792\" height=\"214\" src=\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png\" alt=\"Db2 EXCEPT\" class=\"wp-image-378\" srcset=\"https:\/\/www.db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png 792w, https:\/\/www.db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT-300x81.png 300w, https:\/\/www.db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT-768x208.png 768w\" sizes=\"auto, (max-width: 792px) 100vw, 792px\" \/><\/figure>\n\n\n\n<p>The except of T1 and T2 returns A which is the distinct rows from the T1 result set that does not appear in the T2 result set.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Db2 EXCEPT example<\/h2>\n\n\n\n<p>We&#8217;ll use the <code>customers<\/code> and <code>contacts<\/code> tables created in the <a href=\"https:\/\/db2tutorial.com\/db2-basics\/db2-join\/\">join tutorial<\/a> for the demonstration:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"134\" height=\"97\" src=\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/db2-join-customers-table.png\" alt=\"db2 join customers table\" class=\"wp-image-269\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"122\" height=\"99\" src=\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/db2-join-contacts-table.png\" alt=\"db2 join contacts table\" class=\"wp-image-268\"\/><\/figure>\n\n\n\n<p>The following example uses the <code>EXCEPT<\/code> operator to find the customers who are not in the <code>contacts<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n\t<span class=\"hljs-keyword\">name<\/span>\n<span class=\"hljs-keyword\">FROM<\/span> \n\tcustomers\n<span class=\"hljs-keyword\">EXCEPT<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span> \n\t<span class=\"hljs-keyword\">name<\/span>\n<span class=\"hljs-keyword\">FROM<\/span> \n\tcontacts;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Here is the output:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">NAME                                                                                                 \n-----------\nJessica                                                                                              \nLily             <\/code><\/span><\/pre>\n\n\n<p>In this tutorial, you have learned how to use the Db2 <code>EXCEPT<\/code> to return the result of the first subselect minus any matching rows from the result set of the second subselect.<\/p>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"377\"\n\t\t\t\tdata-post-url=\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/\"\n\t\t\t\tdata-post-title=\"Db2 EXCEPT\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"377\"\n\t\t\t\tdata-post-url=\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/\"\n\t\t\t\tdata-post-title=\"Db2 EXCEPT\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, you will learn how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":141,"menu_order":24,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-377","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Db2 EXCEPT Operator Explained By Practical Examples<\/title>\n<meta name=\"description\" content=\"In this tutorial, you will learn how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.\" \/>\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.db2tutorial.com\/db2-basics\/db2-except\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Db2 EXCEPT Operator Explained By Practical Examples\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you will learn how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/\" \/>\n<meta property=\"og:site_name\" content=\"DB2 Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2020-06-30T06:31:48+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/\",\"url\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/\",\"name\":\"Db2 EXCEPT Operator Explained By Practical Examples\",\"isPartOf\":{\"@id\":\"https:\/\/www.db2tutorial.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png\",\"datePublished\":\"2019-05-26T12:01:23+00:00\",\"dateModified\":\"2020-06-30T06:31:48+00:00\",\"description\":\"In this tutorial, you will learn how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#primaryimage\",\"url\":\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png\",\"contentUrl\":\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.db2tutorial.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Db2 Basics\",\"item\":\"https:\/\/www.db2tutorial.com\/db2-basics\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Db2 EXCEPT\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.db2tutorial.com\/#website\",\"url\":\"https:\/\/www.db2tutorial.com\/\",\"name\":\"DB2 Tutorial\",\"description\":\"A Comprehensive DB2 Tutorial\",\"publisher\":{\"@id\":\"https:\/\/www.db2tutorial.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.db2tutorial.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.db2tutorial.com\/#organization\",\"name\":\"Db2 Tutorial\",\"url\":\"https:\/\/www.db2tutorial.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.db2tutorial.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/favicon.png\",\"contentUrl\":\"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/favicon.png\",\"width\":500,\"height\":500,\"caption\":\"Db2 Tutorial\"},\"image\":{\"@id\":\"https:\/\/www.db2tutorial.com\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Db2 EXCEPT Operator Explained By Practical Examples","description":"In this tutorial, you will learn how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.","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.db2tutorial.com\/db2-basics\/db2-except\/","og_locale":"en_US","og_type":"article","og_title":"Db2 EXCEPT Operator Explained By Practical Examples","og_description":"In this tutorial, you will learn how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.","og_url":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/","og_site_name":"DB2 Tutorial","article_modified_time":"2020-06-30T06:31:48+00:00","og_image":[{"url":"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/","url":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/","name":"Db2 EXCEPT Operator Explained By Practical Examples","isPartOf":{"@id":"https:\/\/www.db2tutorial.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#primaryimage"},"image":{"@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#primaryimage"},"thumbnailUrl":"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png","datePublished":"2019-05-26T12:01:23+00:00","dateModified":"2020-06-30T06:31:48+00:00","description":"In this tutorial, you will learn how to use the Db2 EXCEPT to return the result of the first subselect minus any matching rows from the result set of the second subselect.","breadcrumb":{"@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#primaryimage","url":"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png","contentUrl":"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/05\/Db2-EXCEPT.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-except\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.db2tutorial.com\/"},{"@type":"ListItem","position":2,"name":"Db2 Basics","item":"https:\/\/www.db2tutorial.com\/db2-basics\/"},{"@type":"ListItem","position":3,"name":"Db2 EXCEPT"}]},{"@type":"WebSite","@id":"https:\/\/www.db2tutorial.com\/#website","url":"https:\/\/www.db2tutorial.com\/","name":"DB2 Tutorial","description":"A Comprehensive DB2 Tutorial","publisher":{"@id":"https:\/\/www.db2tutorial.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.db2tutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.db2tutorial.com\/#organization","name":"Db2 Tutorial","url":"https:\/\/www.db2tutorial.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.db2tutorial.com\/#\/schema\/logo\/image\/","url":"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/favicon.png","contentUrl":"https:\/\/db2tutorial.com\/wp-content\/uploads\/2019\/03\/favicon.png","width":500,"height":500,"caption":"Db2 Tutorial"},"image":{"@id":"https:\/\/www.db2tutorial.com\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages\/377","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/comments?post=377"}],"version-history":[{"count":4,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages\/377\/revisions"}],"predecessor-version":[{"id":1138,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages\/377\/revisions\/1138"}],"up":[{"embeddable":true,"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/pages\/141"}],"wp:attachment":[{"href":"https:\/\/www.db2tutorial.com\/wp-json\/wp\/v2\/media?parent=377"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}