FULL OUTER JOIN availability across databases in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to perform a FULL OUTER JOIN between two tables named employees and departments.

SQL

SELECT employees.id, departments.name FROM employees [1] departments ON employees.dept_id = departments.id;

Drag options to blanks, or click blank then click option'

AINNER JOIN

BRIGHT JOIN

CLEFT JOIN

DFULL OUTER JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INNER JOIN which only returns matching rows.

Using LEFT JOIN or RIGHT JOIN which return unmatched rows from only one side.

The FULL OUTER JOIN returns all records when there is a match in either left or right table.

2fill in blank

medium

Complete the code to perform a FULL OUTER JOIN in PostgreSQL between sales and customers tables.

SQL

SELECT sales.id, customers.name FROM sales [1] customers ON sales.customer_id = customers.id;

Drag options to blanks, or click blank then click option'

AFULL OUTER JOIN

BLEFT JOIN

CCROSS JOIN

DINNER JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using CROSS JOIN which returns Cartesian product.

Using INNER JOIN which excludes unmatched rows.

PostgreSQL supports FULL OUTER JOIN to combine rows from both tables including unmatched rows.

3fill in blank

hard

Fix the error in the SQL code to perform a FULL OUTER JOIN in MySQL, which does not support FULL OUTER JOIN directly.

SQL

SELECT a.id, b.name FROM a [1] b ON a.id = b.a_id UNION SELECT a.id, b.name FROM a [2] b ON a.id = b.a_id WHERE a.id IS NULL OR b.a_id IS NULL;

Drag options to blanks, or click blank then click option'

AFULL OUTER JOIN

BRIGHT JOIN

CLEFT JOIN

DINNER JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using FULL OUTER JOIN directly in MySQL causes syntax error.

Using INNER JOIN excludes unmatched rows.

MySQL does not support FULL OUTER JOIN directly. We simulate it by combining LEFT JOIN and RIGHT JOIN with UNION.

4fill in blank

hard

Fill both blanks to simulate FULL OUTER JOIN in MySQL by combining LEFT JOIN and RIGHT JOIN.

SQL

SELECT a.id, b.name FROM a [1] b ON a.id = b.a_id UNION SELECT a.id, b.name FROM a [2] b ON a.id = b.a_id WHERE a.id IS NULL OR b.a_id IS NULL;

Drag options to blanks, or click blank then click option'

ALEFT JOIN

BRIGHT JOIN

CINNER JOIN

DFULL OUTER JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using FULL OUTER JOIN directly in MySQL.

Using INNER JOIN which excludes unmatched rows.

To simulate FULL OUTER JOIN in MySQL, use LEFT JOIN in the first query and RIGHT JOIN in the second query combined with UNION.

5fill in blank

hard

Fill all three blanks to write a query that simulates FULL OUTER JOIN in MySQL using UNION of LEFT JOIN and RIGHT JOIN.

SQL

SELECT [1], [2] FROM table1 LEFT JOIN table2 ON table1.id = table2.id UNION SELECT [3], table2.name FROM table1 RIGHT JOIN table2 ON table1.id = table2.id WHERE table1.id IS NULL;

Drag options to blanks, or click blank then click option'

Atable1.id

Btable2.name

Dtable1.name

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns inconsistently between the two SELECT statements.

Not including the WHERE clause to filter unmatched rows in the RIGHT JOIN.

The first SELECT returns table1.id and table2.name. The second SELECT returns table1.id and table2.name to combine all rows simulating FULL OUTER JOIN.