SET Operation in SQL, UNION, UNION ALL, Intersect and Minus

SET Operation

SQL underpins few Set operations to be performed on tables. These are utilized to get meaningful and important results from the dataset, under various exceptional conditions.

UNION

UNION is utilized to join the outputs of two or more than two Select statement. In any case, it will wipe out copy columns from its outcome set. In the event of union, number of columns and their datatypes must be same in both the tables otherwise the union will not work.

Syntax:

            SELECT col_name_1, col_name_2, col_name_3, …
            FROM tbl_name_1
UNION
SELECT col_name_1, col_name_2, col_name_3, …
            FROM tbl_name_2

Demo Tables
Table 1 Visitors_at_Gate_1

ID Name Cell#
1 Richard Edward +44 123 456-7890
2 Garry Kristein +44 123 456-7980
3 Michael David +44 123 456-8790
4 Rita White +44 123 456-6786
5 Bob Alvin +44 123 456-4568

Table 2 Visitors_at_Gate_2

ID Name Cell#
1 Rachael Stock +44 123 456-1234
2 Garry Kirstein +44 123 456-7980
3 Isabella Rise +44 123 456-3456
4 Rita White +44 123 456-6786
5 Jarome Kahn +44 123 456-6656

Example

SELECT * FROM Visitors_at_Gate_1

UNION

SELECT * FROM Visitors_at_Gate_2

The output of the above is as follows:

ID Name Cell#
1 Rachael Stock +44 123 456-1234
1 Richard Edward +44 123 456-7890
2 Garry Kirstein +44 123 456-7980
3 Isabella Rise +44 123 456-3456
3 Michael David +44 123 456-8790
4 Rita White +44 123 456-6786
5 Bob Alvin +44 123 456-4568
5 Jarome Kahn +44 123 456-6656

UNION ALL

Unlike UNION, UNION ALL shows all the rows regardless if they are duplicate

Syntax

            SELECT col_name_1, col_name_2, col_name_3, …
            FROM tbl_name_1
UNION ALL
SELECT col_name_1, col_name_2, col_name_3, …
            FROM tbl_name_2

Example

SELECT * FROM Visitors_at_Gate_1

UNION ALL

SELECT * FROM Visitors_at_Gate_2

Output of the above query will be:

ID Name Cell#
1 Richard Edward +44 123 456-7890
1 Rachael Stock +44 123 456-1234
2 Garry Kristein +44 123 456-7980
2 Garry Kirstein +44 123 456-7980
3 Michael David +44 123 456-8790
3 Isabella Rise +44 123 456-3456
4 Rita White +44 123 456-6786
4 Rita White +44 123 456-6786
5 Bob Alvin +44 123 456-4568
5 Jarome Kahn +44 123 456-6656

Intersect

Intersect operation is utilized to consolidate the output of two SELECT statements, however it will just return the records that are same from both SELECT statements. In intersect, the columns and their datatypes must be same.

Syntax

The syntax for intersect is:

SELECT col_name_1, col_name_2, col_name_3, …
            FROM tbl_name_1
UNION ALL
SELECT col_name_1, col_name_2, col_name_3, …
            FROM tbl_name_2

Example

SELECT * FROM Visitors_at_Gate_1

INTERSECT

SELECT * FROM Visitors_at_Gate_2

The output of the above query will be:

ID Name Cell#
2 Garry Kirstein +44 123 456-7980
4 Rita White +44 123 456-6786

Minus

Minus operation merges the results of two Select statements and extracts only the results that belongs to the first set and are not matching the second set.

Syntax

The syntax for intersect is:

SELECT col_name_1, col_name_2, col_name_3, …
            FROM tbl_name_1
MINUS
SELECT col_name_1, col_name_2, col_name_3, …
            FROM tbl_name_2

Example

SELECT * FROM Visitors_at_Gate_1

MINUS

SELECT * FROM Visitors_at_Gate_2

The output of the above query will be:

ID Name Cell#
1 Richard Edward +44 123 456-7890
3 Michael David +44 123 456-8790
5 Bob Alvin +44 123 456-4568

More Related Articles For You

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes

Search

Shortcodes Ultimate

 
Follow Us
 
BGP