SQL SET Operators

As the name suggests, a SET operator will give you a combined results of two or more queries. The point that needs to remember is all the queries should fetch the same number of columns and the data type of the columns should match.

Lets discuss, with examples to avoid confusion. We will use two tables, Workers & Labourers.

Table -  Workers

Table - Labourers

Lets start with the SET Operators -

• UNION -  If you use UNION Clause to fetch the combined results from both the above tables, result set will contain the distinct records, thus it automatically eliminates the duplicate records. See the screen shot below -

Here,  for example, "Shelley Higgins" is present in both the tables, but 'UNION' shows only once.

• UNION ALL - UNION ALL behaves the same way, as UNION does, except, it doesn't eliminate the duplicate rows. See below -

• INTERSECT - The 'Intersect" Set operator fetch the common records, from both the tables. See the results below. You might get confused with the "Inner Join". Lets sort it out -

Notice -  The result set displaying the records from 'Workers' table, as its in the left hand side of the query. Thus, intersecting portion of the 'Workers' table is fetched into the result.
Differences between "Intersect" and "Inner Join"
Now when you perform "Intersect", query parses the entire record from the table i.e. it finds out common record including all the columns. But, in case of "Inner Join", it only performs Join operation on the specified or mentioned columns that is Joining.
There is another difference - "Intersect" always gives you all the distinct records, while "Inner Join" may give you duplicates.
Note - What if we use "Inner Join" with a "Distinct" Clause -  Well, here Inner Join behaves exactly like a "Intersect", while retrieving the common columns from both the Tables. 
Lets see the result of the following query -

• MINUS - It behaves as the name suggests. Result set will eliminate the records from the first table, which are present in the second table, while doing a "Minus" of second from the first. See the results -

So, whats the difference between Full Outer Join & MINUS operator.

Can they fetch same results - No, absolutely not. 'Minus' will display the records from the left table i.e. 'from where they want to minus'. But any 'Outer Join' will display result from both tables, only the values will be NULL, if not matched.


That's enough of set operator !! Good Luck !!