Test Your Microsoft SQL Server UNION and UNION ALL Knowledge with This Quiz

Progress

True / False

The UNION keyword combines results from multiple SELECT statements and removes duplicate rows by default.

True False

The UNION keyword combines results from multiple SELECT statements and removes duplicate rows, which is why it returns only distinct rows.

True / False

UNION ALL will return only unique rows from the combined SELECT statements.

True False

UNION ALL returns all rows from the combined SELECT statements, including duplicates. It does not filter out duplicate rows.

True / False

Using UNION requires that all SELECT statements have the same number of columns with compatible data types.

True False

For UNION to work, each SELECT statement must have the same number of columns, and the data types of the columns must be compatible.

True / False

UNION ALL is more performant than UNION because it does not remove duplicate rows.

True False

UNION ALL is generally more performant than UNION because UNION ALL does not require the database to perform the additional work of removing duplicate rows.

True / False

The number of columns in each SELECT statement is not important when using UNION.

True False

The number of columns must be the same in each SELECT statement when using UNION or UNION ALL. The columns must also have compatible data types.

True / False

UNION ALL can be used to combine results from SELECT statements with different column names.

True False

UNION ALL can combine results even if the column names are different, as long as the number and data types of columns match across SELECT statements.

True / False

In a UNION ALL operation, if one of the SELECT statements has a WHERE clause, it will not affect the other SELECT statements.

True False

In a UNION ALL operation, each SELECT statement operates independently, so the WHERE clause in one SELECT statement does not affect the others.

True / False

Using UNION ALL with large datasets might result in performance issues due to duplicate rows being included.

True False

UNION ALL includes all rows from the combined SELECT statements, which can lead to performance issues if the datasets are large and include many duplicate rows.

True / False

UNION and UNION ALL are both set operations that can be used to combine the results of multiple SELECT queries.

True False

Both UNION and UNION ALL are set operations used to combine the results of multiple SELECT queries, but UNION removes duplicates while UNION ALL includes all rows.

Q10

True / False

UNION cannot be used if the SELECT statements have different numbers of columns.

True False

UNION requires that all SELECT statements have the same number of columns with compatible data types. If the number of columns differs, UNION will not work.

Q11

True / False

The ORDER BY clause in a UNION query affects only the final result set, not the individual SELECT statements.

True False

When using UNION, the ORDER BY clause is applied to the final result set after the individual SELECT statements have been combined. It does not affect the order of rows in the individual SELECT statements.

Q12

True / False

UNION ALL will return rows in the order they appear in the SELECT statements, without sorting them.

True False

UNION ALL returns rows in the order they appear in the SELECT statements unless an ORDER BY clause is used. It does not sort the results by default.

Q13

True / False

Using UNION ALL between two queries with different column names will result in an error.

True False

UNION ALL does not require the column names to be the same between queries, but the number and data types of columns must match.

Q14

True / False

UNION ALL is less efficient than UNION when dealing with large datasets due to the additional processing required to remove duplicates.

True False

UNION ALL does not perform any duplicate removal, making it more efficient than UNION, which involves additional processing to remove duplicates.

Q15

True / False

You can use UNION ALL to combine result sets from SELECT statements with different column names as long as the data types match.

True False

UNION ALL requires that the number and data types of columns match across all SELECT statements, but the column names do not have to be the same.

Q16

Single Choice

Which SQL keyword is used to combine the results of two or more SELECT statements while removing duplicates?

UNION UNION ALL INTERSECTEXCEPT

The UNION keyword combines the results of two or more SELECT statements while removing duplicates.

Q17

Single Choice

What does UNION ALL do with duplicate rows from the SELECT statements?

Includes all rows, including duplicates. Excludes duplicate rows. Only includes unique rows.Sorts rows and removes duplicates.

UNION ALL includes all rows from the SELECT statements, including duplicates.

Q18

Single Choice

When using UNION, how are the columns from each SELECT statement combined?

Based on the order and data types of columns. Based on column names. Based on the position of columns in the SELECT statement.By sorting columns alphabetically.

UNION combines columns from each SELECT statement based on their order and data types, not their names.

Q19

Single Choice

Which of the following will be included in the result set of a UNION query?

All unique rows from the combined SELECT statements. All rows from the first SELECT statement only. All rows from the second SELECT statement only.All rows from both SELECT statements including duplicates.

A UNION query will include all unique rows from the combined SELECT statements.

Q20

Single Choice

Which SQL keyword will not remove duplicates from the combined result set?

UNION ALL UNION INTERSECTEXCEPT

UNION ALL does not remove duplicates from the result set, unlike UNION which removes duplicates.

Q21

Single Choice

In a query with UNION, how can you ensure the results are sorted?

By using the ORDER BY clause at the end of the UNION query. By using the ORDER BY clause in each individual SELECT statement. By applying the SORT keyword.By specifying column names in the SELECT statements.

To sort results from a UNION query, you use the ORDER BY clause at the end of the entire UNION query, not within individual SELECT statements.

Q22

Single Choice

Can UNION be used to combine result sets from SELECT statements with different numbers of columns?

No, all SELECT statements must have the same number of columns. Yes, as long as the data types of columns match. Yes, but only the columns with the same names are combined.No, but UNION ALL can handle different numbers of columns.

UNION requires that all SELECT statements have the same number of columns with compatible data types.

Q23

Single Choice

What is a potential drawback of using UNION ALL with large datasets?

Potential performance issues due to including all rows, including duplicates. It automatically sorts the results. It reduces the result set size by removing duplicates.It only includes rows from the first SELECT statement.

UNION ALL includes all rows from the SELECT statements, which can result in performance issues and larger result sets if there are many duplicates.

Q24

Single Choice

Which of the following is true about the use of UNION in SQL queries?

UNION removes duplicates, UNION ALL includes all rows. UNION ALL removes duplicates, UNION includes all rows. UNION and UNION ALL both remove duplicates.UNION and UNION ALL both include only unique rows.

UNION combines results from multiple SELECT statements and removes duplicate rows, whereas UNION ALL includes all rows without removing duplicates.

Q25

Single Choice

In a UNION ALL operation, what happens if one SELECT statement includes a WHERE clause and another does not?

The WHERE clause affects only the rows in its own SELECT statement. The WHERE clause affects all SELECT statements in the UNION ALL operation. The WHERE clause is ignored.The WHERE clause modifies the combined result set.

The WHERE clause in one SELECT statement does not affect the other SELECT statements in a UNION ALL operation. Each SELECT statement is processed independently.

Q26

Single Choice

What will be the output of the following query? `SELECT Col1 FROM TableA UNION SELECT Col1 FROM TableB;`

All unique rows from TableA and TableB. All rows from TableA only. All rows from TableB only.All rows including duplicates from both TableA and TableB.

This query will return all unique rows from both TableA and TableB. Duplicates between the tables will be removed.

Q27

Single Choice

What is a common use case for UNION ALL in SQL queries?

Combining result sets while retaining all rows for analysis. Removing duplicates from result sets. Sorting combined results automatically.Filtering rows based on specific conditions.

UNION ALL is often used when you need to combine result sets from multiple SELECT statements and you want to include all rows, including duplicates, for comprehensive data analysis.

Q28

Single Choice

Which of the following queries will not work if the number of columns in each SELECT statement differs?

SELECT Col1 FROM TableA UNION SELECT Col1, Col2 FROM TableB; SELECT Col1 FROM TableA UNION ALL SELECT Col1 FROM TableB; SELECT Col1 FROM TableA UNION SELECT Col1 FROM TableB;SELECT Col1 FROM TableA UNION ALL SELECT Col1 FROM TableB;

UNION and UNION ALL both require that all SELECT statements have the same number of columns with compatible data types. A mismatch in column numbers will result in an error.

Q29

Single Choice

How can you ensure that a UNION query returns results in a specific order?

By using the ORDER BY clause at the end of the UNION query. By using the ORDER BY clause in each SELECT statement. By specifying ORDER BY in the UNION ALL query.By sorting results after retrieving the data.

To ensure results are returned in a specific order, you should use the ORDER BY clause at the end of the entire UNION query. Ordering in individual SELECT statements does not affect the final result set order.

Q30

Single Choice

What happens if you apply an ORDER BY clause within a UNION ALL query?

It affects only the result set of the specific SELECT statement. It affects the combined result set. It sorts the rows in each SELECT statement before combining.It sorts rows after combining them.

An ORDER BY clause within a UNION ALL query affects only the result set of that specific SELECT statement, not the combined result set. To sort the entire result set, use ORDER BY at the end of the UNION ALL query.

Q31

Multiple Choice

Which of the following code snippets correctly uses UNION to combine results from two SELECT statements?

SQL Code

SELECT Col1 FROM TableA
UNION
SELECT Col1 FROM TableB;

SELECT Col1 FROM TableA UNION SELECT Col1 FROM TableB; SELECT Col1 FROM TableA UNION ALL SELECT Col1 FROM TableB; SELECT Col1 FROM TableA INTERSECT SELECT Col1 FROM TableB;SELECT Col1 FROM TableA EXCEPT SELECT Col1 FROM TableB;

This code snippet correctly uses UNION to combine results from two SELECT statements, removing duplicates from the final result set.

Q32

Multiple Choice

Which SQL keyword can be used to combine results from multiple SELECT statements, including duplicates?

SQL Code

SELECT Col1 FROM TableA
UNION ALL
SELECT Col1 FROM TableB;

UNION ALL UNION INTERSECTEXCEPT

UNION ALL combines results from multiple SELECT statements and includes all rows, including duplicates.

Q33

Multiple Choice

What is the correct way to use UNION ALL in SQL to combine results from three different tables?

SQL Code

SELECT Col1 FROM TableA
UNION ALL
SELECT Col1 FROM TableB
UNION ALL
SELECT Col1 FROM TableC;

SELECT Col1 FROM TableA UNION ALL SELECT Col1 FROM TableB UNION ALL SELECT Col1 FROM TableC; SELECT Col1 FROM TableA UNION SELECT Col1 FROM TableB UNION SELECT Col1 FROM TableC; SELECT Col1 FROM TableA INTERSECT SELECT Col1 FROM TableB INTERSECT SELECT Col1 FROM TableC;SELECT Col1 FROM TableA EXCEPT SELECT Col1 FROM TableB EXCEPT SELECT Col1 FROM TableC;

This snippet correctly uses UNION ALL to combine results from three SELECT statements, including all rows from each table.

Q34

Multiple Choice

Which code snippet will include all rows from the SELECT statements, including duplicates?

SQL Code

SELECT Col1 FROM TableA
UNION ALL
SELECT Col1 FROM TableB;

SELECT Col1 FROM TableA UNION ALL SELECT Col1 FROM TableB; SELECT Col1 FROM TableA UNION SELECT Col1 FROM TableB; SELECT Col1 FROM TableA INTERSECT SELECT Col1 FROM TableB;SELECT Col1 FROM TableA EXCEPT SELECT Col1 FROM TableB;

This code snippet uses UNION ALL to combine rows from TableA and TableB, including all rows and duplicates.

Q35

Multiple Choice

Which of the following is a valid application of the UNION keyword?

SQL Code

SELECT Col1 FROM TableA
UNION
SELECT Col1 FROM TableB;

SELECT Col1 FROM TableA UNION SELECT Col1 FROM TableB; SELECT Col1 FROM TableA INTERSECT SELECT Col1 FROM TableB; SELECT Col1 FROM TableA EXCEPT SELECT Col1 FROM TableB;SELECT Col1 FROM TableA UNION ALL SELECT Col1 FROM TableB;

This code snippet uses UNION to combine results from TableA and TableB, removing duplicate rows from the final result set.

Q36

Multiple Choice

Which SQL operation would you use to find common rows between two SELECT statements?

SQL Code

SELECT Col1 FROM TableA
INTERSECT
SELECT Col1 FROM TableB;

INTERSECT UNION UNION ALLEXCEPT

INTERSECT is used to find common rows between two SELECT statements. It returns only the rows that appear in both queries.

Q37

Multiple Choice

What is the effect of applying the EXCEPT keyword between two SELECT statements?

SQL Code

SELECT Col1 FROM TableA
EXCEPT
SELECT Col1 FROM TableB;

Returns rows from the first SELECT that are not in the second. Returns all rows from both SELECT statements. Returns only rows present in both SELECT statements.Includes all rows from the second SELECT statement.

EXCEPT returns rows from the first SELECT statement that are not present in the second SELECT statement. It removes duplicates from the final result set.

Q38

Multiple Choice

Which of the following operations combines results from multiple queries and eliminates duplicates, but is different from UNION?

INTERSECT UNION ALL EXCEPTUNION

INTERSECT returns only the rows that appear in both SELECT statements, while UNION combines results and removes duplicates.

Q39

Multiple Choice

How does the result set of UNION differ from UNION ALL when combining results from multiple SELECT statements?

UNION removes duplicates, UNION ALL includes all rows. UNION and UNION ALL both remove duplicates. UNION includes all rows, UNION ALL removes duplicates.UNION and UNION ALL both include only unique rows.

UNION removes duplicates from the combined result set, while UNION ALL includes all rows, including duplicates.

Q40

Multiple Choice

What is the result of the following query? `SELECT Col1 FROM TableA UNION ALL SELECT Col1 FROM TableB WHERE Col1 > 100;`

SQL Code

SELECT Col1 FROM TableA
UNION ALL
SELECT Col1 FROM TableB
WHERE Col1 > 100;

All rows from TableA and TableB where Col1 > 100, including duplicates. All rows from TableA only. All rows from TableB where Col1 > 100 only.Unique rows from both TableA and TableB where Col1 > 100.

This query combines all rows from TableA and the rows from TableB where Col1 is greater than 100, including all duplicates from both SELECT statements.

Q41

Multiple Choice

Which SQL operation would you use to return rows that are present in one SELECT statement but not in another?

SQL Code

SELECT Col1 FROM TableA
EXCEPT
SELECT Col1 FROM TableB;

EXCEPT UNION UNION ALLINTERSECT

EXCEPT returns rows from the first SELECT statement that are not present in the second SELECT statement.

Q42

Multiple Choice

How can you combine result sets from different tables with the same column names but different column data types using UNION?

Use CAST or CONVERT to align data types before combining. UNION does not require compatible data types. Combine columns with different data types directly.Data types must be the same but column names can differ.

When combining result sets with UNION, ensure that the columns have compatible data types. You may need to use CAST or CONVERT functions to align data types.

Q43

Multiple Choice

What will be the result of the following query? SELECT Col1 FROM TableA UNION SELECT Col1 FROM TableB UNION ALL SELECT Col1 FROM TableC;

SQL Code

SELECT Col1 FROM TableA
UNION
SELECT Col1 FROM TableB
UNION ALL
SELECT Col1 FROM TableC;

All unique rows from TableA and TableB combined with all rows from TableC. All rows from TableA and TableB combined, including duplicates, with all rows from TableC. Unique rows from TableA combined with unique rows from TableB, excluding TableC.All rows from TableA, TableB, and TableC combined with duplicates.

The query combines results from TableA and TableB using UNION (removing duplicates), and then combines the result with TableC using UNION ALL (including all rows from TableC).

Q44

Multiple Choice

Which of the following code snippets uses UNION to combine result sets and ensure that only unique rows are returned?

SQL Code

SELECT Col1 FROM TableA
UNION
SELECT Col1 FROM TableB;

The UNION keyword combines result sets from two SELECT statements and removes duplicate rows from the final result set.

Q45

Multiple Choice

What will be the result of this query? SELECT Col1 FROM TableA UNION SELECT Col1 FROM TableB UNION ALL SELECT Col1 FROM TableC WHERE Col1 NOT IN (SELECT Col1 FROM TableA);

SQL Code

SELECT Col1 FROM TableA
UNION
SELECT Col1 FROM TableB
UNION ALL
SELECT Col1 FROM TableC
WHERE Col1 NOT IN (SELECT Col1 FROM TableA);

All unique rows from TableA and TableB combined with rows from TableC not in TableA. All rows from TableA and TableB, including duplicates, combined with rows from TableC. All unique rows from TableA, TableB, and TableC.Unique rows from TableA combined with rows from TableB, including rows from TableC in TableA.

The query combines results from TableA and TableB using UNION (removing duplicates), and then combines the result with rows from TableC that are not in TableA using UNION ALL.

Microsoft SQL Server Database Quiz Questions