FULL OUTER JOIN availability across databases in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Which databases support FULL OUTER JOIN natively?

Consider these popular SQL databases: MySQL, PostgreSQL, SQL Server, and SQLite. Which of them supports FULL OUTER JOIN as a native SQL feature?

A None support FULL OUTER JOIN natively

B PostgreSQL and SQL Server only

C All four support FULL OUTER JOIN natively

D MySQL and SQLite only

💡 Hint

Think about which databases have FULL OUTER JOIN as a direct keyword in their SQL syntax.

PostgreSQL and SQL Server support FULL OUTER JOIN directly. MySQL and SQLite do not have native FULL OUTER JOIN syntax.

❓ query_result

intermediate

What is the output of this FULL OUTER JOIN query in PostgreSQL?

Given two tables:

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

Table B:
id | city
2 | Paris
3 | Tokyo

What is the result of:

SELECT A.id, A.name, B.city FROM A FULL OUTER JOIN B ON A.id = B.id ORDER BY A.id, B.id;

SQL

CREATE TABLE A (id INT, name TEXT);
INSERT INTO A VALUES (1, 'Alice'), (2, 'Bob');
CREATE TABLE B (id INT, city TEXT);
INSERT INTO B VALUES (2, 'Paris'), (3, 'Tokyo');

SELECT A.id, A.name, B.city FROM A FULL OUTER JOIN B ON A.id = B.id ORDER BY A.id, B.id;

A

1 | Alice | NULL
2 | Bob   | Paris
3 | NULL  | Tokyo

B

1 | Alice | NULL
2 | Bob   | Paris

C

1 | Alice | NULL
2 | Bob   | Paris
NULL | NULL | Tokyo

D

2 | Bob   | Paris
3 | NULL  | Tokyo

💡 Hint

FULL OUTER JOIN returns all rows from both tables, matching where possible.

The FULL OUTER JOIN returns all rows from both tables. For id=1, only A has data, so city is NULL. For id=2, both tables have data. For id=3, only B has data, so name is NULL.

📝 Syntax

advanced

Which query correctly simulates FULL OUTER JOIN in MySQL?

MySQL does not support FULL OUTER JOIN natively. Which of these queries correctly simulates a FULL OUTER JOIN between tables A and B on column id?

A SELECT A.id, A.name, B.city FROM A LEFT JOIN B ON A.id = B.id UNION ALL SELECT B.id, A.name, B.city FROM B LEFT JOIN A ON B.id = A.id WHERE A.id IS NULL;

B SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id;

C SELECT A.id, A.name, B.city FROM A INNER JOIN B ON A.id = B.id;

D SELECT A.id, A.name, B.city FROM A LEFT JOIN B ON A.id = B.id UNION SELECT B.id, A.name, B.city FROM B LEFT JOIN A ON B.id = A.id;

💡 Hint

FULL OUTER JOIN can be simulated by combining LEFT JOIN and RIGHT JOIN results with UNION.

Option D uses LEFT JOIN and UNION to combine all rows from both tables, simulating FULL OUTER JOIN. Option D uses UNION ALL but misses filtering correctly. Option D is INNER JOIN only. Option D is invalid syntax in MySQL.

❓ optimization

advanced

Which optimization improves FULL OUTER JOIN performance in SQL Server?

You have a large FULL OUTER JOIN query joining tables A and B on id. Which optimization can improve performance?

A Create indexes on the join columns in both tables

B Use CROSS JOIN instead of FULL OUTER JOIN

C Remove indexes on join columns to speed up scanning

D Use subqueries without indexes

💡 Hint

Indexes help the database find matching rows faster.

Indexes on join columns speed up matching rows in joins. Removing indexes or using CROSS JOIN will degrade performance or change results.

🔧 Debug

expert

Why does this FULL OUTER JOIN query fail in SQLite?

Given this query:

SELECT A.id, B.id FROM A FULL OUTER JOIN B ON A.id = B.id;

Why does it fail in SQLite?

A SQLite does not support FULL OUTER JOIN syntax

B The ON clause is missing a condition

C Table aliases are required in SQLite

D SQLite requires USING instead of ON for joins

💡 Hint

Check SQLite's join capabilities.

SQLite does not support FULL OUTER JOIN syntax at all, so the query fails with a syntax error.