FULL OUTER JOIN in PostgreSQL - Mini Project: Build & Apply

Understanding FULL OUTER JOIN in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You have two tables: authors and books. Some authors have written books, but some authors have not yet published any books. Also, some books might be listed without a known author yet.

🎯 Goal: Learn how to use FULL OUTER JOIN in PostgreSQL to combine the authors and books tables, showing all authors and all books, matching where possible, and including unmatched rows from both tables.

📋 What You'll Learn

Create a table called authors with columns author_id (integer) and author_name (text).

Create a table called books with columns book_id (integer), title (text), and author_id (integer).

Insert specific data into both tables as instructed.

Write a FULL OUTER JOIN query to combine authors and books on author_id.

Select author_name and title from the joined result.

💡 Why This Matters

🌍 Real World

FULL OUTER JOIN is useful when you want to see all records from two related tables, even if some records don't have matches. For example, showing all authors and all books, including authors without books and books without authors.

💼 Career

Database developers and analysts often use FULL OUTER JOIN to combine datasets comprehensively, ensuring no data is missed in reports or data analysis.

1

Create the authors table and insert data

Create a table called authors with columns author_id (integer) and author_name (text). Then insert these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

PostgreSQL

-- Create the authors table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the books table and insert data

Create a table called books with columns book_id (integer), title (text), and author_id (integer). Then insert these exact rows: (101, 'Book A', 1), (102, 'Book B', 2), (103, 'Book C', 4).

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Create the books table and insert data
-- Your code here

Hint

Remember to include the author_id column to link books to authors.

3

Write the FULL OUTER JOIN query

Write a SQL query that uses FULL OUTER JOIN to join authors and books on author_id. Select author_name and title from the result.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE books (
    book_id INTEGER,
    title TEXT,
    author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Book A', 1),
(102, 'Book B', 2),
(103, 'Book C', 4);

-- Write the FULL OUTER JOIN query to select author_name and title
-- Your code here

Hint

Use FULL OUTER JOIN to include all rows from both tables, matching on author_id.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by author_name ascending, then by title ascending.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE books (
    book_id INTEGER,
    title TEXT,
    author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Book A', 1),
(102, 'Book B', 2),
(103, 'Book C', 4);

SELECT authors.author_name, books.title
FROM authors
FULL OUTER JOIN books ON authors.author_id = books.author_id
-- Add ORDER BY clause here
-- Your code here

Hint

Use ORDER BY with both columns to sort the output clearly.

Practice

1. What does a FULL OUTER JOIN do in PostgreSQL?

easy

A. It returns all rows from the right table and matching rows from the left table.

B. It returns only rows that have matching values in both tables.

C. It returns all rows from the left table and matching rows from the right table.

D. It returns all rows from both tables, matching where possible, and NULLs where no match exists.

Solution

1. Step 1: Understand the join types

   INNER JOIN returns only matching rows; LEFT JOIN returns all left table rows plus matches; RIGHT JOIN returns all right table rows plus matches.

2. Step 2: Define FULL OUTER JOIN behavior

   FULL OUTER JOIN returns all rows from both tables, matching where possible, and fills unmatched columns with NULL.

3. Final Answer:

   It returns all rows from both tables, matching where possible, and NULLs where no match exists. -> Option D

4. Quick Check:

   FULL OUTER JOIN = all rows both tables [OK]

Hint: Remember: FULL OUTER JOIN = all rows from both tables [OK]

Common Mistakes:

• Confusing FULL OUTER JOIN with INNER JOIN
• Thinking FULL OUTER JOIN returns only left or right table rows
• Assuming unmatched rows are excluded

2. Which of the following is the correct syntax for a FULL OUTER JOIN in PostgreSQL?

easy

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

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

C. SELECT * FROM table1 FULL JOIN table2 WHERE table1.id = table2.id;

D. SELECT * FROM table1 OUTER FULL JOIN table2 ON table1.id = table2.id;

Solution

1. Step 1: Recall PostgreSQL FULL OUTER JOIN syntax

   The correct syntax is: SELECT ... FROM table1 FULL OUTER JOIN table2 ON condition;

2. Step 2: Check each option

   The syntax 'SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id;' matches exactly. Others err: 'JOIN FULL OUTER' wrong order; 'FULL JOIN ... WHERE' misuses WHERE for join; 'OUTER FULL JOIN' wrong sequence.

3. Final Answer:

   SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   FULL OUTER JOIN syntax = FULL OUTER JOIN ON [OK]

Hint: FULL OUTER JOIN always uses 'FULL OUTER JOIN ... ON' syntax [OK]

Common Mistakes:

• Swapping JOIN and FULL OUTER keywords
• Omitting ON clause
• Using WHERE instead of ON for join condition

3. Given these tables:
Table A:
ID | Name
1 | Alice
2 | Bob
4 | Diana

Table B:
ID | City
2 | Paris
3 | London
4 | Berlin

What is the result of:
SELECT COALESCE(A.ID, B.ID) AS ID, A.Name, B.City FROM A FULL OUTER JOIN B ON A.ID = B.ID ORDER BY ID;

medium

A. [{2, 'Bob', 'Paris'}, {3, NULL, 'London'}, {4, 'Diana', 'Berlin'}]

B. [{1, 'Alice', NULL}, {2, 'Bob', NULL}, {3, NULL, 'London'}, {4, 'Diana', 'Berlin'}]

C. [{1, 'Alice', NULL}, {2, 'Bob', 'Paris'}, {3, NULL, 'London'}, {4, 'Diana', 'Berlin'}]

D. [{1, 'Alice', NULL}, {2, 'Bob', 'Paris'}, {4, 'Diana', 'Berlin'}]

Solution

1. Step 1: Identify matching and unmatched IDs

   Matching IDs: 2 and 4. Unmatched in A: 1. Unmatched in B: 3.

2. Step 2: Construct FULL OUTER JOIN result

   Rows:
   - ID 1: Name 'Alice', City NULL (no match in B)
   - ID 2: Name 'Bob', City 'Paris' (match)
   - ID 3: Name NULL, City 'London' (no match in A)
   - ID 4: Name 'Diana', City 'Berlin' (match)

3. Final Answer:

   [{1, 'Alice', NULL}, {2, 'Bob', 'Paris'}, {3, NULL, 'London'}, {4, 'Diana', 'Berlin'}] -> Option C

4. Quick Check:

   FULL OUTER JOIN includes all rows with NULLs for unmatched [OK]

Hint: Match IDs, include all rows, NULL for missing side [OK]

Common Mistakes:

• Excluding unmatched rows from either table
• Mixing up NULL placement
• Ordering rows incorrectly

4. Consider this query:
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.name = 'Alice';
Why might this query return fewer rows than expected?

medium

A. Because the WHERE clause filters out rows where A.name is NULL, excluding unmatched rows from B.

B. Because FULL OUTER JOIN only returns matching rows.

C. Because the ON condition is incorrect syntax.

D. Because the query needs GROUP BY to include all rows.

Solution

1. Step 1: Understand FULL OUTER JOIN with WHERE clause

   FULL OUTER JOIN returns all rows, but WHERE filters after join. Filtering on A.name excludes rows where A.name is NULL (unmatched in A).

2. Step 2: Effect of filtering on unmatched rows

   Rows unmatched in A have NULL for A.name, so WHERE A.name = 'Alice' removes them, reducing rows.

3. Final Answer:

   Because the WHERE clause filters out rows where A.name is NULL, excluding unmatched rows from B. -> Option A

4. Quick Check:

   WHERE filters after join, can exclude NULLs [OK]

Hint: Use WHERE carefully; it filters after join, may exclude NULLs [OK]

Common Mistakes:

• Thinking FULL OUTER JOIN ignores WHERE filters
• Assuming ON clause filters rows after join
• Believing GROUP BY affects join row count

5. You have two tables:
Employees(emp_id, name) and Departments(dept_id, emp_id, dept_name).
You want a list of all employees and their departments if any, plus all departments and their employees if any.
Which query correctly uses FULL OUTER JOIN to achieve this?

hard

A. SELECT e.emp_id, e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.emp_id = d.emp_id;

B. SELECT e.emp_id, e.name, d.dept_name FROM Employees e FULL OUTER JOIN Departments d ON e.emp_id = d.emp_id;

C. SELECT e.emp_id, e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.emp_id = d.emp_id;

D. SELECT e.emp_id, e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.emp_id = d.emp_id;

Solution

1. Step 1: Understand the requirement

   We want all employees and their departments if any, plus all departments and their employees if any. This means all rows from both tables.

2. Step 2: Choose the correct join type

   FULL OUTER JOIN returns all rows from both tables, matching where possible. LEFT or RIGHT JOIN only return all rows from one table.

3. Final Answer:

   SELECT e.emp_id, e.name, d.dept_name FROM Employees e FULL OUTER JOIN Departments d ON e.emp_id = d.emp_id; -> Option B

4. Quick Check:

   FULL OUTER JOIN returns all rows both tables [OK]

Hint: Use FULL OUTER JOIN to include all rows from both tables [OK]

Common Mistakes:

• Using LEFT or RIGHT JOIN excludes unmatched rows from one table
• Using INNER JOIN excludes unmatched rows
• Forgetting ON condition