SET OPERATORS

  • Set operators in Oracle SQL are tools for combining or comparing result sets from different SELECT statements.

They include:

  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS
  • These operators perform operations akin to mathematical set operations, facilitating data merging and extraction across multiple tables.
  • Set operators are crucial for effective data analysis and retrieval in Oracle databases, allowing you to eliminate duplicates and organize data efficiently.

Rules for Unions:

  • same number of colums in both tables
  • you may have to create fields in the other select statement to make the tables equal
  • compatible data types
  • COLUMN NAMES are taken from the lest hand most left column.

UNION VS UNION ALL

In SQL, the UNION operator is used to combine the result sets of two or more SELECT statements. It returns a distinct set of rows from the combined result sets.
On the other hand, the UNION ALL operator also combines result sets, but it includes all rows, including duplicates.

UNION
Returns distinct rows from both tables.

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

UNION ALL
Returns every row from all tables, including duplicates.

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

Intersect

Minus

Returns only unique rows returned by the first query but not by the second:

The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top