SQL Interview Prep · Lesson

Column Count and Type Compatibility

The rules result sets must satisfy to be combined and common mismatch errors.

Column Count and Type Compatibility is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Compatibility Rules Interviewers Probe

Every set operation (UNION, INTERSECT, EXCEPT) requires the two result sets to be union-compatible. Interviewers ask this to see if you know the silent ways these queries break.

Two rules govern compatibility: matching column count and compatible data types position by position. Get either wrong and you get an error or a surprising result.

Rule 1: Same Number of Columns

Both queries must project the same number of columns. If the first query returns three columns and the second returns two, the database rejects the statement.

This is the single most common set-operation error in interviews, especially after someone edits one branch and forgets the other.

SELECT id, name, city FROM a
UNION
SELECT id, name FROM b;
-- ERROR: each UNION query must have the same number of columns

Rule 2: Compatible Types by Position

Columns are matched by position, not by name. The first column of query one pairs with the first column of query two, and so on. Each pair must have compatible types.

Compatible usually means implicitly convertible: an INT and a BIGINT are fine; an INT and a DATE generally are not.

SELECT id, signup_date FROM a
UNION
SELECT id, signup_date FROM b;
-- positions: (int,date) vs (int,date) -> OK

Position, Not Name, Is What Matters

A subtle trap: the columns can have different names and still combine, as long as positions line up by type. Conversely, matching names in the wrong order will misalign the data.

Always read set operations column-by-position. Interviewers plant swapped columns to test this.

SELECT name, id FROM a
UNION ALL
SELECT id, name FROM b;
-- name<->id swapped: types may clash or data lands in wrong column

The Output Column Names

The result set takes its column names from the first query. The second query's labels are discarded. To control headers, alias the columns in the first branch.

This is why a downstream ORDER BY must reference the first query's names or use ordinal positions.

SELECT id AS user_id, city AS location FROM a
UNION ALL
SELECT id, town FROM b;
-- output headers: user_id, location

Implicit Type Coercion

When two positionally-paired columns have different but compatible types, the engine picks a result type wide enough to hold both. An INT combined with a DECIMAL usually yields DECIMAL.

Mentioning this coercion in an interview signals depth: you know the column does not error, it promotes to the broader type.

SELECT amount FROM int_table   -- INT
UNION ALL
SELECT amount FROM dec_table;  -- DECIMAL
-- result column is DECIMAL

Padding Missing Columns With NULL

When two queries naturally differ in shape, you align them by adding placeholder columns. A common technique is selecting NULL (often cast to a type) to fill a slot the other branch has.

Casting the NULL avoids the engine guessing a type and keeps the columns compatible.

SELECT id, phone, CAST(NULL AS VARCHAR) AS email FROM a
UNION ALL
SELECT id, CAST(NULL AS VARCHAR), email FROM b;

A Worked Type-Mismatch Failure

Here the second column is a date in one branch and a string in another. Strict engines reject this; lenient ones may coerce unpredictably. The safe fix is an explicit CAST so both sides agree.

In interviews, propose the cast rather than relying on implicit conversion.

SELECT id, CAST(event_date AS VARCHAR) FROM a
UNION ALL
SELECT id, label FROM b;
-- both second columns now VARCHAR -> compatible

These Rules Apply to All Set Operators

The column-count and type-compatibility rules are identical for UNION, UNION ALL, INTERSECT, and EXCEPT. They all stack result sets vertically and all require union-compatible inputs.

So one mental checklist covers every set operation: count columns, check types by position, control names from the first query.

SELECT id, city FROM a
INTERSECT
SELECT id, city FROM b;
-- same compatibility rules as UNION

The Pre-Flight Checklist

Before combining result sets, verify:

Same number of columns in every branch.
Each positional pair is type-compatible (cast if unsure).
Columns are in the same logical order.
Aliases set on the first query for clean output names.

Running this checklist out loud in an interview prevents the silent misalignment bugs.

Tying It Together

Compatibility failures are mostly avoidable. The errors are loud when column counts differ and silent when columns are swapped or coerced unexpectedly. Reading queries column-by-position and casting deliberately keeps set operations correct.

A confident answer names both rules, mentions positional matching, and notes that output names come from the first query.

Quick Check

How does SQL decide which columns of two UNION branches correspond to each other?

Recap

Key takeaways:

All set operators need the same column count in every branch.
Columns are matched by position, and each pair must be type-compatible.
Compatible types may be coerced to a wider common type.
Use CAST(NULL AS type) to pad shapes that differ.
Output names come from the first query.

Frequently asked questions

Is the “Column Count and Type Compatibility” lesson free?

Yes — the full text of “Column Count and Type Compatibility” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Column Count and Type Compatibility”?

The rules result sets must satisfy to be combined and common mismatch errors. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Column Count and Type Compatibility” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep