Back to: Oracle Tutorials for Beginners and Professionals

Real-Time Examples of SET Operators in Oracle

In this article, I am going to discuss Real-Time Examples of SET Operators in Oracle with Examples. Please read our previous article, where we discussed SET Operators in Oracle with Examples.

Using Set Operations to Compare Two Tables:

Developers, and even DBAs, occasionally need to compare the contents of two tables to determine whether the tables contain the same data. The need to do this is especially in test environments, as developers may want to compare a set of data generated by a program under test with a set of “known good” data. Comparison of tables is also useful for automated testing purposes when we have to compare actual results with a given set of expected results. SQL’s set operations provide an interesting solution to this problem of comparing two tables.

Real-time Example to Understand SET Operators in Oracle:

We are going to use the following CUSTOMER_KNOWN_GOOD and CUSTOMER_TEST tables to understand Real-Time Examples of SET Operators in Oracle.

Please use the below SQL Script to create and populate the CUSTOMER_KNOWN_GOOD and CUSTOMER_TEST tables with the required sample data.

CREATE TABLE CUSTOMER_KNOWN_GOOD
(
   CUST_NBR INT,
   NAME     VARCHAR(20)
);

INSERT INTO CUSTOMER_KNOWN_GOOD VALUES (1, 'Sony');
INSERT INTO CUSTOMER_KNOWN_GOOD VALUES (1, 'Sony');
INSERT INTO CUSTOMER_KNOWN_GOOD VALUES (2, 'Samsung');
INSERT INTO CUSTOMER_KNOWN_GOOD VALUES (3, 'Panasonic');
INSERT INTO CUSTOMER_KNOWN_GOOD VALUES (3, 'Panasonic');
INSERT INTO CUSTOMER_KNOWN_GOOD VALUES (3, 'Panasonic');

CREATE TABLE CUSTOMER_TEST
(
   CUST_NBR INT,
   NAME     VARCHAR(20)
);

INSERT INTO CUSTOMER_TEST VALUES (1, 'Sony');
INSERT INTO CUSTOMER_TEST VALUES (1, 'Sony');
INSERT INTO CUSTOMER_TEST VALUES (2, 'Samsung');
INSERT INTO CUSTOMER_TEST VALUES (2, 'Samsung');
INSERT INTO CUSTOMER_TEST VALUES (3, 'Panasonic');

As you can see the CUSTOMER_KNOWN_GOOD and CUSTOMER_TEST tables have the same structure, but different data. Also, notice that none of these tables has a primary or unique key; there are duplicate records in both. The following SQL will compare these two tables effectively.

The following query uses both MINUS and UNION ALL to compare two tables for equality. The query depends on each table having either a primary key or at least one unique index.

Example:

(SELECT * FROM CUSTOMER_KNOWN_GOOD
MINUS
SELECT * FROM CUSTOMER_TEST)
UNION ALL
(SELECT * FROM CUSTOMER_TEST
MINUS
SELECT * FROM CUSTOMER_KNOWN_GOOD);

When you execute the above query, you will get the following output.

Explanation: Let’s talk a bit about how this query works. We can look at it as the union of two compound queries. The parentheses ensure that both MINUS operations take place first before the UNION ALL operation is performed. The result of the first MINUS query will be those rows in CUSTOMER_KNOWN_GOOD that are not also in CUSTOMER_TEST. The result of the second MINUS query will be those rows in CUSTOMER_TEST that are not also in CUSTOMER_KNOWN_GOOD. The UNION ALL operator simply combines these two result sets for convenience. If no rows are returned by this query, then we know that both tables have identical rows. Any rows returned by this query represent differences between the CUSTOMER_TEST and CUSTOMER_KNOWN_GOOD tables.

Example:

(SELECT C1.*, COUNT(*) FROM CUSTOMER_KNOWN_GOOD C1
GROUP BY C1.CUST_NBR, C1.NAME
MINUS
SELECT C2.*, COUNT(*) FROM CUSTOMER_TEST C2
GROUP BY C2.CUST_NBR, C2.NAME)
UNION ALL
(SELECT C3.*, COUNT(*) FROM CUSTOMER_TEST C3
GROUP BY C3.CUST_NBR, C3.NAME
MINUS
SELECT C4.*, COUNT(*) FROM CUSTOMER_KNOWN_GOOD C4
GROUP BY C4.CUST_NBR, C4.NAME);

When you execute the above query, you will get the following output.

Explanation: These results indicate that one table (CUSTOMER_KNOWN_GOOD) has one record for “Samsung”, whereas the second table (CUSTOMER_TEST) has two records for the same customer. Also, one table (CUSTOMER_KNOWN_GOOD) has three records for “Panasonic”, whereas the second table (CUSTOMER_TEST) has one record for the same customer. Both the tables have the same number of rows (two) for “Sony”, and therefore “Sony” doesn’t appear in the output.

TIP: Duplicate rows are not possible in tables that have a primary key or at least one unique index. Use the short form of the table comparison query for such tables.

Using NULLs in Compound Queries in Oracle

We discussed union compatibility conditions at the beginning of this chapter. The union compatibility issue gets interesting when NULLs are involved. As we know, NULL doesn’t have a datatype, and NULL can be used in place of a value of any datatype. If we purposely select NULL as a column value in a component query, Oracle no longer has two datatypes to compare in order to see whether the two-compound queries are compatible. For character columns, this is no problem. For example:

SELECT 1 NUM, 'DEFINITE' STRING FROM DUAL
UNION
SELECT 2 NUM, NULL STRING FROM DUAL;

When you execute the above query, you will get the following output.

Notice that Oracle considers the character string ‘DEFINITE’ from the first component query to be compatible with the NULL value supplied for the corresponding column in the second component query. However, if a NUMBER or a DATE column of a component query is set to NULL, we must explicitly tell Oracle what “flavor” of NULL to use. Otherwise, we’ll encounter errors. For

Example:

SELECT 1 NUM, 'DEFINITE' STRING FROM DUAL
UNION
SELECT NULL NUM, 'UNKNOWN' STRING FROM DUAL;

ERROR at line 1: ORA-01790: expression must have the same datatype as the corresponding expression

Note that the use of NULL in the second component query causes a data type mismatch between the first column of the first component query and the first column of the second component query. Using NULL for a DATE column causes the same problem, as in the following.

Example:

SELECT 1 NUM, SYSDATE DATES FROM DUAL
UNION
SELECT 2 NUM, NULL DATES FROM DUAL;

SELECT 1 NUM, SYSDATE DATES FROM DUAL

ERROR at line 1: ORA-01790: expression must have the same datatype as the corresponding expression

In these cases, we need to cast the NULL to a suitable datatype to fix the problem, as in the following examples:

SELECT 1 NUM, 'DEFINITE' STRING FROM DUAL
UNION
SELECT TO_NUMBER(NULL) NUM, 'UNKNOWN' STRING FROM DUAL;

When you execute the above query, you will get the following output.

Example:

SELECT 1 NUM, SYSDATE DATES FROM DUAL
UNION
SELECT 2 NUM, TO_DATE(NULL) DATES FROM DUAL;

When you execute the above query, you will get the following output.

This problem of union compatibility when using NULLs is encountered in Oracle8i. However, there is no such problem in Oracle9i, as we can see in the following examples generated from an Oracle9i database.

SELECT 1 NUM, 'DEFINITE' STRING FROM DUAL
UNION
SELECT NULL NUM, 'UNKNOWN' STRING FROM DUAL;

When you execute the above query, you will get the following output.

Example:

SELECT 1 NUM, SYSDATE DATES FROM DUAL
UNION
SELECT 2 NUM, NULL DATES FROM DUAL;

When you execute the above query, you will get the following output.

In the next article, I am going to discuss IS NULL Operator in Oracle with Examples. Here, in this article, I try to explain Real-Time Examples of SET Operators in Oracle with Examples and I hope you enjoy this Real-Time Examples of SET Operators in Oracle with Examples article. If you have any queries regarding the Real-Time Examples of SET Operators in Oracle, then please let us know by putting your query in the comment section.