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

Need a 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

Need a 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

Need a 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

Need a hint?

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