{"id":23,"date":"2020-01-06T12:00:36","date_gmt":"2020-01-06T12:00:36","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=23"},"modified":"2024-02-09T13:39:41","modified_gmt":"2024-02-09T18:39:41","slug":"oracle-set-operators-union-intersect-minus","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/oracle-set-operators-union-intersect-minus\/","title":{"rendered":"Oracle set operators: union, intersect, minus"},"content":{"rendered":"\n<p>The most commonly used set operators in Oracle are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>union<\/li><li>intersect<\/li><li>minus<\/li><\/ul>\n\n\n\n<p><em>Union<\/em>&nbsp;Union combine results returned by two or queries into a single table. In a union, all tables must have matching columns. It shows all rows from the first query and all rows from the second query while removing duplicate entries. It has the syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>query1 union query2<\/code><\/pre>\n\n\n\n<p>For example, we have a query1<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where gender = 'M' order by salary<\/code><\/pre>\n\n\n\n<p>and a query2<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where gender = 'F' order by salary<\/code><\/pre>\n\n\n\n<p>A union would be<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where gender = 'M'\nunion\nselect name, salary from employee where gender = 'F' order by salary<\/code><\/pre>\n\n\n\n<p>Query1 shows names and salaries of all male employees. Query2 shows names and salaries of all female employees. The union query shows the names and salaries of all male and female employees.<\/p>\n\n\n\n<p>Note that I have removed the order by clause from query1. Queries inside a union cannot be ordered. However, the resulting rows from a union can be ordered.<\/p>\n\n\n\n<p><em>Intersect<\/em>&nbsp;Intersect operator takes results from two queries and returns only the rows that appear in both results. Syntax<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>query1 intersect query2<\/code><\/pre>\n\n\n\n<p>For example, we have a query1<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where salary > 100000<\/code><\/pre>\n\n\n\n<p>and a query2<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where gender = 'F'<\/code><\/pre>\n\n\n\n<p>An intersect would be<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where gender = 'M'\nintersect\nselect name, salary from employee where gender = 'F' order by salary<\/code><\/pre>\n\n\n\n<p>Query1 shows names and salaries of all employees making over 100k a year. Query2 shows names and salaries of all female employees. The intersect table shows names and salaries of all female employees making over 100k.<\/p>\n\n\n\n<p><em>Minus<\/em>&nbsp;Minus operator takes results from two queries and returns only the rows that appear in the results of the first query but not the second. Syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>query1 minus query2<\/code><\/pre>\n\n\n\n<p>For example, we have a query1<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where salary > 100000<\/code><\/pre>\n\n\n\n<p>and a query2<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where gender = 'F'<\/code><\/pre>\n\n\n\n<p>An intersect would be<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name, salary from employee where gender = 'M'\nminus\nselect name, salary from employee where gender = 'F' order by salary<\/code><\/pre>\n\n\n\n<p>Query1 shows names and salaries of all employees making over 100k a year. Query2 shows names and salaries of all female employees. The minus table shows names and salaries of all male employees making over 100k.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The most commonly used set operators in Oracle are: union intersect minus Union&nbsp;Union combine results returned by two or queries into a single table. In a union, all tables must have matching columns. It shows all rows from the first query and all rows from the second query while removing duplicate entries. It has the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,515],"tags":[14,3],"class_list":["post-23","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle","tag-database","tag-oracle"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/23","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=23"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/23\/revisions"}],"predecessor-version":[{"id":24,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/23\/revisions\/24"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=23"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=23"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=23"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}