Set operation column matching rules in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UNION with different column names

Consider two tables:

Table A:
id | name
1 | Alice
2 | Bob

Table B:
user_id | username
2 | Bob
3 | Charlie

What is the output of the following SQL query?

SELECT id, name FROM A
UNION
SELECT user_id, username FROM B;

SQL

CREATE TABLE A (id INT, name VARCHAR(10));
INSERT INTO A VALUES (1, 'Alice'), (2, 'Bob');
CREATE TABLE B (user_id INT, username VARCHAR(10));
INSERT INTO B VALUES (2, 'Bob'), (3, 'Charlie');

SELECT id, name FROM A
UNION
SELECT user_id, username FROM B;

A

1 | Alice
3 | Charlie

B

1 | Alice
2 | Bob
3 | Charlie

C Syntax error due to different column names

D

1 | Alice
2 | Bob
2 | Bob
3 | Charlie

💡 Hint

UNION matches columns by position, not by name.

In set operations like UNION, columns are matched by their position, not by their names. So the first column of A matches the first column of B, and the second column matches the second. UNION removes duplicates, so Bob appears only once.

🧠 Conceptual

intermediate

Column count requirement in set operations

Which of the following statements about set operations (UNION, INTERSECT, EXCEPT) is true regarding the number of columns in the SELECT statements?

A Both SELECT statements must have the same number of columns.

B The first SELECT can have more columns than the second.

C The second SELECT can have more columns than the first.

D The number of columns does not matter as long as the data types match.

💡 Hint

Think about how rows are combined in set operations.

Set operations require both SELECT statements to have the same number of columns because rows are combined positionally.

📝 Syntax

advanced

Error caused by mismatched column counts

What error will this SQL query produce?

SELECT id, name FROM A
UNION
SELECT user_id FROM B;

A Error: Data types of columns do not match

B No error, query runs and returns combined rows

C Error: Column names must match exactly

D Error: Each SELECT statement must have the same number of columns

💡 Hint

Check the number of columns in each SELECT.

The first SELECT returns two columns, the second only one. Set operations require the same number of columns.

❓ optimization

advanced

Improving performance of UNION ALL vs UNION

Given two large tables with identical column structures, which set operation is generally faster and why?

Options:

SELECT * FROM A
UNION ALL
SELECT * FROM B;

vs

SELECT * FROM A
UNION
SELECT * FROM B;

A UNION is faster because it removes duplicates efficiently.

B Both have the same performance because they scan the same data.

C UNION ALL is faster because it does not remove duplicates.

D UNION ALL is slower because it returns all rows including duplicates.

💡 Hint

Think about what extra work UNION does compared to UNION ALL.

UNION removes duplicates which requires sorting or hashing, adding overhead. UNION ALL simply concatenates results without extra processing.

🔧 Debug

expert

Diagnosing unexpected output from INTERSECT with mismatched data types

Consider these tables:

Table X:
id INT
1
2
3

Table Y:
id VARCHAR(10)
'2'
'3'
'4'

What will be the result of this query?

SELECT id FROM X
INTERSECT
SELECT id FROM Y;

A Empty result set because data types differ and no implicit conversion occurs.

B Syntax error due to different data types in INTERSECT.

C Rows with id 2 and 3 because values match despite different types.

D Rows with id 1, 2, and 3 because INTERSECT ignores data types.

💡 Hint

Check how INTERSECT compares values with different data types.

INTERSECT compares values including data types. Since INT and VARCHAR are different, no matches occur without explicit casting.