Unlock hundreds more features
Save your Quiz to the Dashboard
View and Export Results
Use AI to Create Quizzes and Analyse Results

OR
Sign inSign in with Facebook
Sign inSign in with Google
Quizzes > Computers & Technology > Programming & Software

Master the SQL UNION Operator Quiz

Think you know how SQL UNION handles duplicates? Start the quiz!

Difficulty: Moderate
2-5mins
Learning OutcomesCheat Sheet
Paper art illustration representing SQL UNION quiz on a golden yellow background

Get ready to sharpen your skills with the Master SQL UNION: Does It Remove Duplicates? Take this free quiz! Whether you're a budding data analyst or seasoned developer, this free challenge quiz is your gateway to mastering the sql union operator and answering the burning question: does sql union remove duplicates - and boosting your confidence in database querying. You'll explore sql union examples, compare sql union distinct vs all, and dive into essential sql set operations. Looking for a quick warm-up? Check out our SQL quiz or level up with the SQL fill-in-the-blanks quiz . Ready to test yourself and improve your query game? Dive in now!

What does the SQL UNION operation do by default?
Combines result sets and removes duplicates
Returns Cartesian product of both result sets
Combines result sets and keeps duplicates
Returns only rows common to both result sets
By default, UNION merges two result sets and eliminates duplicate rows to return a distinct set of records. This behavior ensures that each row appears only once in the combined output. If you need to preserve all occurrences, you must use UNION ALL instead. Microsoft Docs
Which SQL keyword removes duplicate rows when combining two queries?
EXCEPT
UNION ALL
UNION
INTERSECT
The UNION operator automatically removes duplicates in the final result, whereas UNION ALL retains them. INTERSECT returns only common rows, and EXCEPT returns rows in the first result set that aren't in the second. For pure deduplication in a combination, use UNION. PostgreSQL Docs
To include all duplicates when combining result sets, which keyword should you use?
UNION ALL
DISTINCT
UNION
GROUP BY
UNION ALL concatenates result sets without checking for duplicate rows, thus preserving all duplicates. It is useful when you need a true append of data with no deduplication. Using DISTINCT or GROUP BY would reintroduce a deduplication step. Oracle Docs
Which mathematical set operation is most similar to SQL's UNION?
Cartesian product
Set union
Set difference
Set intersection
SQL UNION corresponds to the union operation in set theory, combining all unique elements from two sets. Intersection returns only common elements, and difference returns elements in one set but not the other. Cartesian product pairs each element from one set with every element of the other. Wikipedia
Which operation generally has better performance, UNION or UNION ALL?
Performance depends solely on network latency
They have identical performance
UNION
UNION ALL
UNION ALL is faster because it does not perform the duplicate-elimination step required by UNION. Removing duplicates typically involves sorting or hashing, which adds overhead. When you don't need deduplication, always prefer UNION ALL for speed. SQL Shack
What happens if two SELECT statements in a UNION have different numbers of columns?
The engine aligns common columns automatically
The query fails with an error
Extra columns are silently dropped
Missing columns are padded with NULLs
UNION requires each SELECT to return the same number of columns with compatible data types. If the counts differ, the database raises an error. All SELECTs must be structurally compatible before being combined. PostgreSQL Docs
Can you use ORDER BY clauses in each individual SELECT within a UNION?
No, ORDER BY is only allowed after the final SELECT
Only in UNION ALL, not in UNION
Yes, but only if enclosed in parentheses
Yes, each SELECT can have its own ORDER BY
ORDER BY can only follow the last SELECT in a UNION clause to sort the combined results. Individual SELECTs within a UNION cannot include ORDER BY unless wrapped as subqueries. This ensures the final ordering is applied to the merged set. MySQL Docs
Which clause determines the column names in the result set of a UNION query?
The first SELECT's column names or aliases
The UNION keyword's own alias list
Alphabetical order of all column names
The last SELECT's column names or aliases
The final result set uses column names or aliases defined in the first SELECT statement. Subsequent SELECTs only contribute data, not naming. This rule keeps output consistent and predictable. Microsoft Docs
How are NULL values treated when evaluating duplicates in a UNION operation?
Every NULL is treated as distinct
Only the first NULL is kept, others dropped
NULLs in corresponding columns are considered equal
NULLs cause the UNION to fail
Most SQL implementations treat NULLs in corresponding columns as equal for duplicate elimination. Therefore, rows where all columns match including NULL positions are considered duplicates. This behavior lets many NULL-containing rows be collapsed. Oracle Base
Which of the following best describes the relationship between UNION and DISTINCT?
DISTINCT can be used only on single SELECTs, not on UNIONs
DISTINCT and UNION are unrelated operations
UNION ALL is identical to DISTINCT
UNION applies DISTINCT on the combined result set
UNION performs a DISTINCT on the combined result set, effectively eliminating duplicates. DISTINCT applied to a single SELECT behaves the same as that SELECT wrapped in a UNION with itself, but UNION is the standard set operator. GeeksforGeeks
How does SQL Server internally perform duplicate elimination for a UNION?
Utilize a bitmap filter to drop duplicates
Use a hash match distinct operator only
Apply nested loops to compare every row
Sort the combined rows and remove duplicates
SQL Server's query optimizer typically implements UNION by sorting the combined rows and then performing a distinct step to eliminate duplicates. Depending on the execution plan, a hash or other operator may also be used, but the sort + dedup step is common. Understanding this helps in performance tuning. SQL Shack
When combining large result sets where duplicates are rare, which approach can improve performance compared to UNION?
Replace UNION with INTERSECT
Use UNION ALL then ROW_NUMBER() to filter duplicates
Use EXCEPT to first eliminate unique rows
Perform a CROSS JOIN before UNION
Using UNION ALL followed by a window function (ROW_NUMBER) to filter duplicates can be faster than UNION, which always sorts or hashes all rows for deduplication. This approach is beneficial when duplicate rows are very rare. Redgate
What is a potential downside of using UNION ALL with ROW_NUMBER for deduplication?
Increased complexity and higher memory usage for windowing
Results may be returned out of order
It disables parallel execution plans
It forces SQL to use only nested loops
While this approach can be faster when duplicates are rare, it introduces additional complexity and relies on window functions which consume more memory and CPU. Planning and indexing must account for the windowed operation. SQLPerformance.com
Which SQL clause cannot appear before a UNION operator?
GROUP BY
HAVING
ORDER BY
WHERE
ORDER BY can only be applied to the overall UNION result, not to individual SELECT statements, unless those SELECTs are wrapped in subqueries. WHERE, GROUP BY, and HAVING are all valid in each SELECT prior to UNION. MySQL Docs
In cost-based optimizers, how is the cost of UNION DISTINCT typically compared to UNION ALL?
Cost depends solely on the network throughput
UNION DISTINCT cost is always equal to or higher than UNION ALL
Both costs are always identical
UNION DISTINCT cost is always lower than UNION ALL
Distinct requires additional sorting or hashing to eliminate duplicates, so a cost-based optimizer always assigns UNION DISTINCT an equal or greater cost compared to UNION ALL. This reflects the overhead of the deduplication step. Microsoft Docs
0
{"name":"What does the SQL UNION operation do by default?", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"What does the SQL UNION operation do by default?, Which SQL keyword removes duplicate rows when combining two queries?, To include all duplicates when combining result sets, which keyword should you use?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Study Outcomes

  1. Understand SQL UNION Operator -

    Grasp how the SQL UNION operator works to merge results from multiple SELECT statements into a single result set.

  2. Determine Duplicate Removal Behavior -

    Identify whether SQL UNION removes duplicate rows by default and explain the role of DISTINCT in this process.

  3. Differentiate UNION vs UNION ALL -

    Compare SQL UNION with UNION ALL to understand when to use each for unique versus all-row combinations.

  4. Apply SQL UNION in Queries -

    Construct effective SQL queries using UNION to combine data from multiple tables and ensure desired result sets.

  5. Analyze Query Results -

    Evaluate the output of UNION operations to verify that duplicates are handled correctly and result sets meet expectations.

  6. Troubleshoot Duplicate Row Issues -

    Diagnose and resolve common problems related to duplicate rows when using SQL UNION or UNION ALL in queries.

Cheat Sheet

  1. Understanding the SQL UNION operator -

    The SQL UNION operator merges the result sets of two or more SELECT statements into a single result, following ANSI SQL standard (ISO/IEC 9075). Each SELECT must have the same number of columns with compatible data types, as documented in Microsoft Docs and Oracle Learning Library.

  2. Default duplicate removal behavior -

    By default, SQL UNION removes duplicate rows, acting like UNION DISTINCT (see IBM developerWorks). For example, combining SELECT 1, 'A' UNION SELECT 1, 'A' returns only one row.

  3. UNION ALL vs. UNION DISTINCT -

    UNION ALL preserves all rows, including duplicates, while UNION (DISTINCT) prunes duplicates. Remember "A for ALL, no de-dup" to distinguish UNION ALL from the default UNION behavior.

  4. Performance considerations -

    Removing duplicates requires a sort or hash operation, which can slow queries on large datasets, per PostgreSQL and SQL Server benchmarks. Use UNION ALL when you know your data sets are already unique to boost performance.

  5. Best practices and examples -

    Always align column order and types, and consider using parentheses for clarity when chaining multiple UNIONs (e.g., (SELECT…) UNION (SELECT…) UNION ALL (SELECT…)). Refer to academic sources like Stanford's SQL course notes for real-world sql union examples.

Make a Quiz (FREE) Copy & Edit This Quiz Create a Quiz with AI

Programming & Software Quizzes

Powered by: Quiz Maker