Which statement is true regarding the UNION operator?
A.
By default, the output is not sorted.
B.
Null values are not ignored during duplicate checking.
C.
Names of all columns must be identical across all select statements.
D.
The number of columns selected in all select statements need not be the same.
Explanation:
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT
statements. It removes duplicate rows between the various SELECT statements.
Each SQL SELECT statement within the UNION query must have the same number of fields
in the result sets with similar data types.
B is correct.
b
It’s B
B would be a better answer than D.
Guidelines to use UNION operator –
• Returns results from both queries after eliminating duplications.
• # of columns and the datatypes of the columns being selected must be identical in all the SELECT statements used in the query.
• The names of the columns need not be identical.
• Operates over all of the columns being selected.
• NULL values are not ignored during duplicate checking.
• By default, the output is sorted in ascending order of the first column of the first SELECT clause.
In summary, UNION returns the combined rows from two queries, sorting them and removing duplicates. Duplicity is measured by the combination of columns and not the individual column separately.
For this question, I think answer D might mean different thing when it was set.
As each query must contain the same number of columns which are compared position by position, NULL can be substituted in place of column which is missing in the other query within the same compound query. In this context, “The number of columns selected in all select statements need not be the same.” but still a bit off.
SELECT employee_id , first_name, job_id
FROM employees
UNION
SELECT employee_id , NULL “first_name”, job_id
FROM job_history;