Set Operations In SQL With Examples: The set operators are availed to combine information of similar type from one or more than one table. The set operators look similar to SQL joins although there is a big difference. SQL joins tends to combine columns from different tables, whereas SQL set operators combine rows from distinct queries. There are distinct types of set operators in SQL. Set Operator in SQL are same as of DBMS. This article will cover all the set operations in SQL like Union, Union All, Intersect, Minus with Examples.

Also Read: SQL Functions in Details

  • Union
  • Union all
  • Intersect
  • Minus

Also Read: Various Keys in DBMS

Set Operations In SQL With Examples

The UNION set operator returns the combined results of the 2 choose statements. Essentially, it removes duplicates from the results i.e. just one row is listed for every duplicated result. To counter this behavior, use the UNION ready operator that retains the duplicates within the effect. INTERSECT lists solely records that are common to each the choose queries; the MINUS set operator removes the second query’s results from the output if they’re additionally found within the initial query’s results. Intersect and MINUS set operations to produce unduplicated results. A detailed explanation of these operators is mentioned below:

Also Read: What is Database Management System

1. Union

This set operator is used to combine the outputs of two or more queries into a single set of rows and columns having different records.

UNION Set Operator In SQL
R=UNION(A,B)

Example Of UNION

Table A

 
Roll No. Name
234 Mark
235 Steve
236 Harry

Table B

 
Roll No. Name
236 Harry
237 James
238 Jessica

UNION Set Operator SQL Query

SQL> SELECT * FROM A

UNION

SELECT * FROM B

Result of the above UNION Operator will be

Roll No. Name
234 Mark
235 Steve
236 Harry
237 James
238 Jessica

Also See: Pagination in SQL Server, MySQL and Oracle with Examples

2. Union All

This set operator is used to join the outputs of two or more queries into a single set of rows and columns without the removal of any duplicates.

UNION All Set Operator in SQL
R=UNION ALL(A,B)

Example of UNION ALL

Table A

 
Roll No. Name
234 Mark
235 Steve
236 Harry

Table B

 
Roll No. Name
236 Harry
237 James
238 Jessica

UNION ALL Set Operator SQL Query

SQL> SELECT * FROM A

UNION ALL

SELECT * FROM B

Result of the above UNION ALL Operator will be

Roll No. Name
234 Mark
235 Steve
236 Harry
236 Harry
237 James
238 Jessica

3. INTERSECT

This set operator is availed to retrieve the information which is common in both tables. The number of columns and data type must be same in intersect set operator.

INTERSECT Set Operator in SQL
R=INTERSECT(A,B)

Example on INTERSECT Operator

Table A

 
Roll No. Name
234 Mark
235 Steve
236 Harry

Table B

 
Roll No. Name
236 Harry
237 James
238 Jessica

INTERSECT Set Operator Query

SQL> SELECT * FROM A

INTERSECT

SELECT * FROM B

Result of the above INTERSECT Operator will be

Roll No. Name
236 Harry

NOTE: MYSQL does not support INTERSECT set operator

Also Read: Explain RDBMS in Detail

4. MINUS

This set operator is availed to retrieve the information of one table which is not available in another table.

Minus Set Operator in SQLac
R=MINUS(A,B)

Example of MINUS Operator

Table A

 
Roll No. Name
234 Mark
235 Steve
236 Harry

Table B

 
Roll No. Name
236 Harry
237 James
238 Jessica

MINUS Set Operator SQL Query

SQL> SELECT * FROM A

MINUS

SELECT * FROM B

Result of the above MINUS Operator will be

Roll No. Name
234 Mark
235 Steve

Important points of set operators:

  • INTERSECT operator and UNION operator is commutative.
  • Performance wise, UNION ALL shows higher performance as compared to UNION as a result of resources aren’t wasted in filtering duplicates and sorting the result set.
  • Set operators are the part of subqueries.
  • Set operators cannot be utilized to choose statements containing TABLE assortment expressions.
  • The LONG, BLOB, CLOB, BFILE, VARRAY, or nested table aren’t allowable to be used in Set operators. For an update, a clause isn’t allowed with the set operators.

So it was all about Set Operators in SQL with examples, if you have any query then please comment below and tell us.

Set Operations In SQL With Examples: UNION, UNION ALL, INTERSECT, MINUSSumit ThakurWhat Is DBMSSet Operations In SQL With Examples: The set operators are availed to combine information of similar type from one or more than one table. The set operators look similar to SQL joins although there is a big difference. SQL joins tends to combine columns from different tables, whereas SQL...Let's Define DBMS