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 questionsThe 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.
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.
- 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.
Keep practicing
Take the free 1Z0-071 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other 1Z0-071 practice questions
- Question 2Which three statements are true about single row functions?
- Question 3Which statement executes successfully?
- Question 4Which two are true about transactions in the Oracle Database?
- Question 6Which two are true about external tables that use the ORACLE _DATAPUMP access driver?
- Question 9Which statement will execute successfully?
- Question 10Examine this statement: CREATE TABTE orders (sarial_no NUMBER UNIQUE, order_id NUMBER PRI…
