Set Operators in Oracle (Union / Intersect / Minus / UnionAll)

Rules of all set operators :

  • Number of columns in the 1st result should be equal to the number of columns in the 2nd result set.
  • The data types of both the result set should match for all the columns.
  • Union
    • Removes Duplicate
    • Sorts the output
    • Not Recommended – Since duplicate removal and sorting
  • Union All
    • Does not removes Duplicates
    • Will not sort the data by default (unless order by)
    • Union all perform better than union since no duplicate removal and sorting
  • Intersect
    • Common will be given as output
    • Output is sorted
    • Removes the duplicate entries
  • Minus
    • Will show the result of first query excluding the result of second query
    • Output is sorted by default
    • Removes the duplicate entry

Leave a Comment