FULL OUTER JOIN availability across databases in SQL - Mini Project: Build & Apply

Explore FULL OUTER JOIN Availability Across Databases

📖 Scenario: You are working with two different databases that store customer orders and customer feedback separately. You want to combine this information to see all customers who have either placed an order, given feedback, or both.

🎯 Goal: Build SQL queries to perform a FULL OUTER JOIN between two tables orders and feedback to find all customers with their order and feedback details, and understand how FULL OUTER JOIN works in different database systems.

📋 What You'll Learn

Create two tables named orders and feedback with specified columns and data.

Add a configuration variable to specify the database system (e.g., 'PostgreSQL', 'MySQL').

Write a FULL OUTER JOIN query to combine orders and feedback on customer_id.

Add a final note or query to handle databases that do not support FULL OUTER JOIN natively.

💡 Why This Matters

🌍 Real World

Combining customer orders and feedback data from different sources to get a complete view of customer interactions.

💼 Career

Understanding FULL OUTER JOIN and its availability helps database professionals write compatible queries across different database systems.

1

Create tables orders and feedback with sample data

Create a table called orders with columns customer_id (integer) and order_amount (integer). Insert these rows exactly: (1, 100), (2, 150), (4, 200). Then create a table called feedback with columns customer_id (integer) and comments (text). Insert these rows exactly: (2, 'Good service'), (3, 'Late delivery'), (4, 'Excellent').

SQL

-- Create table orders and insert data
-- Create table feedback and insert data

Need a hint?

Use CREATE TABLE statements for both tables and INSERT INTO to add the exact rows.

2

Set the database system variable

Create a variable called db_system and set it to the string 'PostgreSQL' to represent the database you are using.

SQL

CREATE TABLE orders (customer_id INT, order_amount INT);
INSERT INTO orders VALUES (1, 100), (2, 150), (4, 200);

CREATE TABLE feedback (customer_id INT, comments TEXT);
INSERT INTO feedback VALUES (2, 'Good service'), (3, 'Late delivery'), (4, 'Excellent');

-- Set the database system variable
db_system = ''  -- Your code here

Need a hint?

Assign the string 'PostgreSQL' to the variable db_system.

3

Write a FULL OUTER JOIN query to combine orders and feedback

Write a SQL query called full_outer_join_query that selects orders.customer_id, order_amount, and comments from orders FULL OUTER JOIN feedback on orders.customer_id = feedback.customer_id.

SQL

CREATE TABLE orders (customer_id INT, order_amount INT);
INSERT INTO orders VALUES (1, 100), (2, 150), (4, 200);

CREATE TABLE feedback (customer_id INT, comments TEXT);
INSERT INTO feedback VALUES (2, 'Good service'), (3, 'Late delivery'), (4, 'Excellent');

db_system = 'PostgreSQL'

full_outer_join_query = """  -- Your code here
"""

Need a hint?

Use FULL OUTER JOIN to combine the tables on customer_id.

4

Add a query for databases without FULL OUTER JOIN support

Write a SQL query called alternative_query that uses UNION of LEFT JOIN and RIGHT JOIN to simulate a FULL OUTER JOIN between orders and feedback on customer_id.

SQL

CREATE TABLE orders (customer_id INT, order_amount INT);
INSERT INTO orders VALUES (1, 100), (2, 150), (4, 200);

CREATE TABLE feedback (customer_id INT, comments TEXT);
INSERT INTO feedback VALUES (2, 'Good service'), (3, 'Late delivery'), (4, 'Excellent');

db_system = 'PostgreSQL'

full_outer_join_query = """
SELECT orders.customer_id, order_amount, comments
FROM orders
FULL OUTER JOIN feedback ON orders.customer_id = feedback.customer_id
"""

alternative_query = """  -- Your code here
"""

Need a hint?

Use LEFT JOIN and RIGHT JOIN combined with UNION to mimic FULL OUTER JOIN.