Can I use Set Operators on different tables?

Set Operators in Oracle

The results of two or more SELECT queries can be combined into a single result set in Oracle SQL using the potent set operators. These processes are essential for combining data from several queries so that thorough data analysis and reporting are possible. UNION, UNION ALL, INTERSECT, and MINUS are the main set operators.

Common Characteristics of Set Operators

There are certain important guidelines to follow when using set operators to combine queries:

• The component queries’ SELECT lists need to include the same quantity of expressions.
• It is necessary for the matching expressions in the SELECT lists to belong to the same datatype group, such as character or numeric, and be of compatible datatypes. If datatypes can be converted implicitly, Oracle Database will try to do so.
• Duplicate rows are automatically removed from the final result set by all set operators (except from UNION ALL).
• Except when specified in brackets, all set operators are evaluated from left to right and have equal precedence. To be clear, it is a good idea to use brackets since INTERSECT will be given more priority in a future Oracle version.
• Set operators are **not valid on columns of BLOB, CLOB, BFILE, VARRAY, or nested tabletypes**. Additionally,UNION, INTERSECT, and MINUSare not valid onLONG` columns.
• Set operations process entire sets of rows, as opposed to row-by-row operations; therefore, the user does not receive the results until the full operation is finished. For online users, this can occasionally result in a perceived performance lag.

To demonstrate these operators, let’s construct two basic tables, X_TABLE and Y_TABLE, and add some integer values to them.

-- Create X_TABLE
CREATE TABLE X_TABLE (
    COL NUMBER
);

-- Insert values into X_TABLE
INSERT INTO X_TABLE (COL) VALUES (1);
INSERT INTO X_TABLE (COL) VALUES (2);
INSERT INTO X_TABLE (COL) VALUES (3);
INSERT INTO X_TABLE (COL) VALUES (4);
INSERT INTO X_TABLE (COL) VALUES (5);
INSERT INTO X_TABLE (COL) VALUES (6);
-- 6 rows created. [11]

-- Create Y_TABLE
CREATE TABLE Y_TABLE (
    COL NUMBER
);

-- Insert values into Y_TABLE
INSERT INTO Y_TABLE (COL) VALUES (5);
INSERT INTO Y_TABLE (COL) VALUES (6);
INSERT INTO Y_TABLE (COL) VALUES (7);
-- 3 rows created. [11]

-- Commit the changes
COMMIT;

Using X_TABLE and Y_TABLE as examples, let’s now look at each set operator.

UNION

The UNION operator returns all unique entries from both queries by combining the result sets of two or more SELECT statements. The final output will only contain a row once if it appears in both result sets. To do this, Oracle uses a sorting operation to find and remove duplicates.

Example: To see all unique values from both X_TABLE and Y_TABLE:

SELECT COL FROM X_TABLE
UNION
SELECT COL FROM Y_TABLE;

Output:

       COL
----------
         1
         2
         3
         4
         5
         6
         7

Explanation: Although the values 5 and 6 are present in both tables, the UNION result only lists them once.

UNION ALL

The UNION ALL operator returns all rows, including duplicates, by combining the result sets of two or more SELECT operations. In general, UNION ALL is faster when duplicate rows are acceptable or wanted because it does not use a sort operation to remove duplicates like UNION does.

Example: To see all values from both X_TABLE and Y_TABLE, including duplicates:

SELECT COL FROM X_TABLE
UNION ALL
SELECT COL FROM Y_TABLE;

Output:

       COL
----------
         1
         2
         3
         4
         5
         6
         5
         6
         7

Explanation: Because they appear in both X_TABLE and Y_TABLE, values 5 and 6 are listed twice.

INTERSECT

All SELECT statements share separate rows, which are the sole rows returned by the INTERSECT operator. It identifies the records that show up in all combined query result sets.

Example: To find values that exist in both X_TABLE and Y_TABLE:

SELECT COL FROM X_TABLE
INTERSECT
SELECT COL FROM Y_TABLE;

Output:

       COL
----------
         5
         6

The explanation is that X_TABLE and Y_TABLE only have values 5 and 6. It’s advisable to think about other methods whenever feasible because INTERSECT might occasionally cause performance problems.

MINUS

The MINUS operator yields unique entries from the initial SELECT statement that are absent from the SELECT queries that follow. Because switching the sequence of the SELECT statements will produce a different result, it is essential for MINUS.

Example 1 (X_TABLE MINUS Y_TABLE): To find values in X_TABLE that are not in Y_TABLE:

SELECT COL FROM X_TABLE
MINUS
SELECT COL FROM Y_TABLE;

Output:

       COL
----------
         1
         2
         3
         4

Explanation: Values 1, 2, 3, and 4 are in X_TABLE but not in Y_TABLE.

Example 2 (Y_TABLE MINUS X_TABLE): To find values in Y_TABLE that are not in X_TABLE:

SELECT COL FROM Y_TABLE
MINUS
SELECT COL FROM X_TABLE;

Output:

       COL
----------
         7

Explanation: The reason is that only value 7 appears in Y_TABLE and not in X_TABLE. This illustrates the substantial influence that the arrangement of tables has on the result of a MINUS operation.

Alternatives and Performance Considerations

While powerful, set operators are often not the only way to achieve desired results. For instance, combining SELECT statements can sometimes be achieved using IN, AND, or OR clauses, or various JOIN types, especially for single-column comparisons. Many SQL users find IN, AND, and OR to be clearer and easier to understand. For example, a MINUS operation can often be rewritten using a NOT EXISTS or NOT IN subquery. Similarly, INTERSECT can be functionally equivalent to an INNER JOIN.

Oracle uses a SORT UNIQUE NOSORT operation to remove duplicates for UNION, INTERSECT, and MINUS. Set operations entail processing entire sets of rows. Performance may be impacted by this sorting, particularly if there are complicated or large-scale datasets involved in the underlying query. By encouraging the optimiser to favour execution pathways that do not conduct set operations, hints such as FIRST_ROWS can potentially speed up the return of the first row to the user if the application does not require all rows to be sorted before presentation.

Consider set operators as a way to combine various lists of items. If you’ve made a list of the fruits and veggies you enjoy:

• Why You get a single list of all the unique produce you enjoy from UNION; there are no duplicates.
• All food, including fruits and vegetables that were on both original lists, is listed together in UNION ALL.
• If, for example, you put “tomato” on both your fruit and vegetable lists, INTERSECT will only show you the things that are on both lists.
• The MINUS (Fruit list MINUS Vegetable list) app only shows you the fruits you enjoy that aren’t on your list of vegetables.

When dealing with different data integration and comparison scenarios in a relational database context, these operators are crucial.

Limitations of Set Operators

Set operators in Oracle SQL (like UNION, UNION ALL, INTERSECT, and MINUS) are used to combine the results of two or more SELECT statements. While powerful, they come with a strict set of limitations you must follow for your queries to work.

1. Same Number of Columns: Every SELECT statement in the combined query must return the exact same number of columns.
2. Compatible Data Types: The data types of corresponding columns in each SELECT statement must be compatible. For example, if the first query’s first column is NUMBER, the second query’s first column must be a NUMBER or a type that can be implicitly converted to a NUMBER (like VARCHAR2 containing only digits). A query will fail if you try to combine a DATE column with a VARCHAR2 column.
3. Column Names from the First Query: The column names and aliases in the final result set are determined solely by the first SELECT statement in the combined query. Any aliases or names in subsequent SELECT statements are ignored.
4. ORDER BY Clause Placement: You can only use a single ORDER BY clause, and it must appear at the very end of the entire compound query. You can’t use ORDER BY within individual SELECT statements.
5. Performance Overhead: UNION, INTERSECT, and MINUS perform a DISTINCT operation to remove duplicate rows. This sorting and comparison process can be slow and intensive, especially for large datasets. UNION ALL is often preferred when duplicates are acceptable because it’s much faster as it skips this process.
6. Unsupported Data Types: Set operators cannot be used on columns with specific data types like BLOB, CLOB, BFILE, VARRAY, or nested tables.
7. No FOR UPDATE Clause: The FOR UPDATE clause, which locks rows for DML operations, is not allowed in a query that uses set operators.