In mathematics, there are three key operations between two sets: union, intersection, and difference. Union combines every element from either set into a single set. Intersection identifies elements that reside in both sets simultaneously. Difference determines elements in the first set that does not exist in the second set.
Similarly, Oracle provides set operators, UNION, UNION ALL, INTERSECT, and EXCEPT, which can be applied to two tables. In this context, you can consider tables as mathematical sets and rows as their elements. Just like in math, the Oracle set operators allow you to combine, compare, and manipulate data stored in tables.
Here's a quick breakdown of the corresponding operations:
- UNION: Similar to mathematical union, this operator combines all distinct rows from two or more tables, eliminating duplicates.
- UNION ALL: This one differs from UNION by including all rows from the tables, even duplicates. Think of it as merging the tables side-by-side.
- INTERSECT: This operator finds rows that exist in both tables, essentially like the intersection of two sets in math.
- EXCEPT: This operator identifies rows present in the first table but not in the second, similar to the difference between sets.
To further illustrate, let's consider two tables defined by the following commands:
The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It is a set operation that allows you to unify data from different tables or queries, removing duplicate rows in the process. For example, based on the tables and rows from the figure shown earlier, queries selecting the department_id values from each table result in the sets as follows:
Now, let's apply a UNION operator between the queries:
We see that the resulting set from the UNION operator retrieves every department_id values from the first and second sets, excluding any duplicates.
On the other hand, the UNION ALL operator does not exclude any duplicated values in the resulting set. For example:
The INTERSECT operator returns only the rows that are common to the result of two SELECT statements. It also removes duplicate rows, just as UNION does. For example:
The EXCEPT operator returns only the rows that are uniquely exist in the first result set, but does not present in the second result set. Here, uniquely exist means that the results from the operator will not contain any duplicate values. For example:
Common Syntax Errors in Set Operations
In any set operations, the SELECT statements on either side must have the same number of columns with compatible data types. This is because Oracle want you to ensure that each row in the result set has a well-defined structure. If the number of columns or data types are not compatible, the database system may encounter difficulties in processing data. For example:
Another common error in set operations is the ORDER BY clause in the SELECT statement. If you are using ORDER BY with a set operation, you cannot sort a part of the result set; you must sort the entire result set. Thus, you need to place the ORDER BY clause at the end of the combined result set.
Lastly, please make sure that there are no extra or missing semicolons in your SQL statements. Particularly, an extra semicolon at the end of the first SELECT statement is very common, as typically we write the SELECT queries before applying the set operators between them.
0 Comments