Set operation column matching rules in SQL - Step-by-Step Execution

Concept Flow - Set operation column matching rules

Start: Two SELECT queries

↓

Check number of columns

↓

Check data types compatibility

↓

Perform set operation (UNION, INTERSECT, EXCEPT)

↓

Return combined result with columns from first query

The database checks that both queries have the same number of columns and compatible data types before combining their results.

Execution Sample

SQL

SELECT id, name FROM employees
UNION
SELECT emp_id, emp_name FROM contractors;

Combines rows from employees and contractors tables with matching columns using UNION.

Execution Table

Step	Action	First Query Columns	Second Query Columns	Check Result	Outcome
1	Identify columns	id (int), name (text)	emp_id (int), emp_name (text)	Number of columns equal	Proceed
2	Check data types	int, text	int, text	Data types compatible	Proceed
3	Combine rows	All rows from first query	All rows from second query	Columns matched by position	Result set returned
4	End	-	-	-	Execution complete

💡 Execution stops after combining rows because column counts and types match.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
First Query Columns	Unknown	id (int), name (text)	id (int), name (text)	id (int), name (text)
Second Query Columns	Unknown	emp_id (int), emp_name (text)	emp_id (int), emp_name (text)	emp_id (int), emp_name (text)
Check Result	None	Number of columns equal	Data types compatible	Columns matched by position

Key Moments - 3 Insights

Why must the number of columns be the same in both queries?

Because set operations combine rows by matching columns positionally, different column counts would cause mismatch and errors (see execution_table step 1).

What happens if data types are not compatible?

The database will raise an error and stop execution because it cannot combine columns with incompatible types (see execution_table step 2).

Which query's column names appear in the final result?

The column names from the first query are used in the combined result set (see execution_table step 3).

Visual Quiz - 3 Questions

Look at the execution table, what is checked at step 2?

A Number of columns equality

B Data types compatibility

C Row count equality

D Column names matching

💡 Hint

Refer to the 'Check data types' action in execution_table row 2.

At which step does the database combine rows from both queries?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look for 'Combine rows' action in execution_table.

If the second query had 3 columns instead of 2, what would happen?

A Execution proceeds normally

B Data types compatibility is checked

C Error due to column count mismatch

D Column names from second query used

💡 Hint

See execution_table step 1 about number of columns equality.

Concept Snapshot

Set operations (UNION, INTERSECT, EXCEPT) require both queries to have the same number of columns.
Columns are matched by position, not by name.
Data types of corresponding columns must be compatible.
Final result uses column names from the first query.
If checks fail, the operation errors out.

Full Transcript

When using set operations in SQL like UNION, the database first checks that both SELECT queries have the same number of columns. Then it verifies that the data types of these columns are compatible. If both checks pass, it combines the rows from both queries by matching columns in order. The final result uses the column names from the first query. If the number of columns or data types do not match, the database raises an error and stops execution.