MCQ King

Oracle – 41 – UNION, UNION ALL, INTERSECT and MINUS

Start MCQ

In Oracle SQL, the UNION, UNION ALL, INTERSECT, and MINUS operators are used for combining and manipulating the result sets of multiple SELECT queries. These operators allow you to perform set operations on data retrieved from one or more tables. Here’s a brief description of each of these operators:

1. UNION Operator:

  • Purpose: The UNION operator is used to combine the result sets of two or more SELECT queries into a single result set. It returns distinct rows, removing duplicate rows from the combined result.
  • Syntax: SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;
  • Use Cases: UNION is used when you want to combine data from multiple tables or queries into one result set while eliminating duplicate rows.

2. UNION ALL Operator:

  • Purpose: The UNION ALL operator is similar to UNION but does not remove duplicate rows. It combines the result sets of multiple SELECT queries, including duplicates.
  • Syntax: SELECT column1, column2, ... FROM table1 UNION ALL SELECT column1, column2, ... FROM table2;
  • Use Cases: UNION ALL is used when you want to combine data from multiple tables or queries into a single result set, including all rows (even duplicates).

3. INTERSECT Operator:

  • Purpose: The INTERSECT operator is used to retrieve the common rows that exist in the result sets of two or more SELECT queries.
  • Syntax: SELECT column1, column2, ... FROM table1 INTERSECT SELECT column1, column2, ... FROM table2;
  • Use Cases: INTERSECT is used when you want to find rows that exist in both result sets, effectively performing a set intersection operation.

4. MINUS Operator:

  • Purpose: The MINUS operator is used to retrieve the rows that exist in the first SELECT query but not in the second SELECT query. It effectively performs a set difference operation.
  • Syntax: SELECT column1, column2, ... FROM table1 MINUS SELECT column1, column2, ... FROM table2;
  • Use Cases: MINUS is used when you want to find rows that are present in the first result set but not in the second, helping you identify differences or missing data.

These set operators are valuable for querying and manipulating data when you need to combine, compare, or find intersections and differences between result sets. They are commonly used in various SQL scenarios, including data reporting, data integration, and data validation.