1Z0-071 practice question 22 of 35

Examine this query which executes successfully: SELECT job, deptno FROM emp UNION ALL SELECT job, deptno FROM jobs_ history; What will be the result?

Full set: 326 questions
Answer options
A: It will return rows common to both SELECT statements.
B: It will return rows from both SELECT statements after eliminating duplicate rows.
C: It will return rows that are not common to both SELECT statements.
D: It will return rows from both SELECT statements including duplicate rows. Correct answer
Correct Answer:
D. It will return rows from both SELECT statements including duplicate rows.
Explanation

The UNION ALL operator is a set operator used to combine the result sets of two or more SELECT statements. Unlike the UNION operator, UNION ALL does not perform a distinct sort to eliminate duplicate rows. It simply concatenates the results from the queries, including all rows from both, even if they are identical. Therefore, the query will return all rows from the emp table followed by all rows from the jobshistory table.

Why Incorrect

A. This describes the functionality of the INTERSECT operator, which returns only the rows that are present in both result sets.
B. This describes the functionality of the UNION operator, which combines result sets and then removes any duplicate rows.
C. This does not accurately describe any standard SQL set operator. The MINUS operator returns rows from the first query that are not in the second.

References

- Oracle Database SQL Language Reference, 19c. (2023). The UNION [ALL], INTERSECT, MINUS Operators. In "SQL Language Reference". Retrieved from Oracle Help Center. This section explicitly states, "UNION ALL returns all rows from each query, including all duplicate rows."

- Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill. Chapter 3, "Introduction to SQL," Section 3.5, "Set Operations," clearly differentiates between UNION and UNION ALL, noting that UNION ALL retains all duplicates.

Scroll to Top