Why set operations matter in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this UNION query?

Consider two tables: students_a and students_b, each with a single column name.
students_a has names: Alice, Bob, Charlie.
students_b has names: Bob, Diana, Edward.
What is the result of this query?

SELECT name FROM students_a
UNION
SELECT name FROM students_b;

PostgreSQL

CREATE TABLE students_a(name TEXT);
INSERT INTO students_a VALUES ('Alice'), ('Bob'), ('Charlie');
CREATE TABLE students_b(name TEXT);
INSERT INTO students_b VALUES ('Bob'), ('Diana'), ('Edward');

SELECT name FROM students_a
UNION
SELECT name FROM students_b;

A Alice, Charlie, Diana, Edward

B Alice, Bob, Charlie, Diana, Edward

C Bob, Diana, Edward

D Alice, Bob, Charlie, Bob, Diana, Edward

💡 Hint

UNION removes duplicates and combines all unique rows from both queries.

The UNION operation combines rows from both tables but removes duplicates. Bob appears in both tables but only once in the result.

❓ query_result

intermediate

What does EXCEPT return in this query?

Given the same tables students_a and students_b as before,
what is the output of this query?

SELECT name FROM students_a
EXCEPT
SELECT name FROM students_b;

PostgreSQL

CREATE TABLE students_a(name TEXT);
INSERT INTO students_a VALUES ('Alice'), ('Bob'), ('Charlie');
CREATE TABLE students_b(name TEXT);
INSERT INTO students_b VALUES ('Bob'), ('Diana'), ('Edward');

SELECT name FROM students_a
EXCEPT
SELECT name FROM students_b;

A Alice, Bob, Charlie, Diana, Edward

B Bob

C Diana, Edward

D Alice, Charlie

💡 Hint

EXCEPT returns rows in the first query that are not in the second.

Bob is in both tables, so it is excluded. Alice and Charlie are only in students_a, so they appear.

📝 Syntax

advanced

Which query correctly uses INTERSECT to find common names?

You want to find names that appear in both students_a and students_b. Which query is correct?

A SELECT name FROM students_a INTERSECT SELECT name FROM students_b;

B SELECT name FROM students_a UNION INTERSECT SELECT name FROM students_b;

C SELECT name FROM students_a INTERSECT ALL SELECT name FROM students_b;

D SELECT name FROM students_a EXCEPT INTERSECT SELECT name FROM students_b;

💡 Hint

INTERSECT combines two queries and returns only rows present in both.

Option A is the correct syntax for INTERSECT. Options B and D have invalid syntax. Option A uses INTERSECT ALL, which is supported in PostgreSQL but preserves duplicate rows if they exist in both result sets.

❓ optimization

advanced

Which approach is more efficient to find unique names from two large tables?

You have two large tables with millions of rows each. You want to get all unique names from both tables.
Which query is generally more efficient in PostgreSQL?

A Use INTERSECT to find unique names.

B Use UNION ALL followed by SELECT DISTINCT on the combined result.

C Use UNION which removes duplicates automatically.

D Use EXCEPT to remove duplicates.

💡 Hint

UNION removes duplicates during the operation, avoiding extra steps.

UNION is optimized to remove duplicates during the merge. UNION ALL with DISTINCT requires an extra step and can be slower. INTERSECT and EXCEPT do not return all unique names.

🧠 Conceptual

expert

Why do set operations require queries to have the same number of columns and compatible types?

Set operations like UNION, INTERSECT, and EXCEPT combine results from multiple queries.
Why must these queries have the same number of columns and compatible data types?

A Because set operations combine rows vertically, each column must match in position and type to merge correctly.

B Because set operations join tables horizontally, columns can differ in number and type.

C Because PostgreSQL automatically converts all data types to text during set operations.

D Because set operations only work on numeric columns.

💡 Hint

Think about how rows from different queries stack on top of each other.

Set operations stack rows from queries. Each column position must match in number and type to combine rows properly. Different numbers or incompatible types cause errors.