Learn what the SQL UNION DISTINCT command does and how it combines multiple queries into a single result set while eliminating duplicate rows. Understand its functionality and difference from UNION ALL.
Table of Contents
Question
What does the UNION DISTINCT command do in SQL?
A. Nothing, UNION and DISTINCT cannot be combined
B. Combines two or more queries and eliminates duplicates
C. Combines two or more queries and eliminates NULL rows
D. Combines two or more queries and preserves duplicates
Answer
B. Combines two or more queries and eliminates duplicates
Explanation
Explanation of UNION DISTINCT in SQL
The UNION DISTINCT command in SQL is used to combine the results of two or more SELECT queries into a single result set. By default, it eliminates any duplicate rows that appear across the combined datasets.
Here’s a detailed breakdown:
- Combining Queries: The UNION operator merges the output of multiple SELECT statements into one result set.
- Duplicate Elimination: The default behavior of UNION (or explicitly specifying UNION DISTINCT) ensures that all duplicate rows are removed from the final output. This is achieved by sorting and comparing rows across the result sets.
- Default Behavior: In many relational database management systems (RDBMS), using just UNION is equivalent to UNION DISTINCT as it inherently removes duplicates.
Example
Suppose you have two tables: Table1 and Table2.
Query
SELECT column_name FROM Table1 UNION DISTINCT SELECT column_name FROM Table2;
Result
Combines rows from both tables.
Removes any duplicate rows from the combined result.
Key Differences Between UNION, UNION DISTINCT, and UNION ALL
| Operator | Behavior | Performance Impact |
|---|---|---|
| UNION DISTINCT | Combines results and removes duplicates | Slower due to sorting for deduplication |
| UNION | Equivalent to UNION DISTINCT (default) | Same as above |
| UNION ALL | Combines results but retains duplicates | Faster as no deduplication occurs |
Why Use UNION DISTINCT?
- Data Integrity: Ensures that only unique rows are present in the final output.
- Simplified Queries: Eliminates the need for additional filtering logic like DISTINCT within individual queries.
In summary, the UNION DISTINCT command is an essential SQL tool for combining datasets while ensuring that no duplicate rows exist in the final result set.
Performing Smart Analytics and AI on Google Cloud Platform skill assessment practice question and answer (Q&A) dump including multiple choice questions (MCQ) and objective type questions, with detail explanation and reference available free, helpful to pass the Performing Smart Analytics and AI on Google Cloud Platform exam and earn Performing Smart Analytics and AI on Google Cloud Platform certification.