Oracle SQL Certification 1Z0-071 Course | Section 18: Using SET Operators

In this section, we dive into SQL SET operators, which allow you to combine results from multiple SELECT statements into a single output.

These operators are essential for comparing datasets, removing duplicates, or merging data from multiple sources. Each lesson is accompanied by a video demonstration to help you understand these concepts in practice.

What are SET Operators

In this lesson, we introduce the SET operators in Oracle SQL, which are different from the SET clause used in UPDATE statements. SET operators are used to combine results from multiple queries into one unified output.

The main SET operators include:

  • UNION – Combines results and returns only distinct rows. Duplicates are automatically removed.
  • UNION ALL – Combines results but includes all rows, including duplicates.
  • INTERSECT – Returns only rows that exist in both queries.
  • MINUS – Returns rows from the first query that do not exist in the second query.

Important rules and limitations:

  1. Each query must return the same number of columns.
  2. Corresponding columns must have compatible data types.
  3. LOB types such as CLOB and BLOB cannot be used with set operators.
  4. The ORDER BY clause can only appear after the last SELECT statement, not inside individual queries.

SET operators are highly useful for comparing datasets, combining data from multiple sources, and eliminating duplicate rows.

Combining Multiple Queries – UNION

This lesson focuses on combining SQL queries using UNION and UNION ALL. We create two sample tables to explore how each operator handles duplicates.

  • UNION removes duplicate rows and returns only distinct results.
  • UNION ALL preserves all rows, including duplicates.

Understanding the difference between these operators is crucial for accurate data analysis and reporting.

Combining More Queries – INTERSECT and MINUS

In this lesson, we cover INTERSECT and MINUS operators:

  • INTERSECT returns only the rows that appear in both queries.
  • MINUS returns rows from the first query that do not exist in the second query.

We use sample tables to demonstrate how these operators can efficiently filter and compare datasets, making it easier to extract the information you need.

Combining Multiple Queries – Advanced Combinations

This lesson teaches you how to combine multiple SQL queries using advanced combinations of set operators.

You will learn to:

  • Mix UNION, UNION ALL, INTERSECT, and MINUS.
  • Control operator precedence using brackets.
  • Handle duplicates across multiple queries for precise results.

Mastering these combinations is essential for writing complex queries that efficiently manipulate and merge data.

Ordering Results with SET Operators

Finally, this lesson explains how to order results when using set operators. Key points include:

  • The ORDER BY clause must follow the last query in a set operation.
  • Using column aliases ensures consistent and error-free sorting.
  • Understanding how ordering interacts with UNION, INTERSECT, and MINUS is important for accurate query results.

Proper ordering helps present combined data clearly and logically.