FULL OUTER JOIN behavior in SQL - Mini Project: Build & Apply

Understanding FULL OUTER JOIN Behavior in SQL

📖 Scenario: You work in a small bookstore. You have two lists: one with books currently in stock and another with books ordered but not yet arrived. You want to see all books, whether they are in stock, ordered, or both.

🎯 Goal: Create two tables in_stock and ordered with book titles and quantities. Then write a FULL OUTER JOIN query to combine these tables, showing all books with their stock and order quantities.

📋 What You'll Learn

Create a table called in_stock with columns book_title (text) and quantity_in_stock (integer).

Insert exactly these rows into in_stock: ('The Hobbit', 5), ('1984', 8), ('Dune', 3).

Create a table called ordered with columns book_title (text) and quantity_ordered (integer).

Insert exactly these rows into ordered: ('Dune', 7), ('The Catcher in the Rye', 4), ('1984', 2).

Write a FULL OUTER JOIN query joining in_stock and ordered on book_title.

Select book_title, quantity_in_stock, and quantity_ordered in the result.

💡 Why This Matters

🌍 Real World

Bookstores and inventory systems often need to combine data from current stock and incoming orders to manage inventory effectively.

💼 Career

Understanding FULL OUTER JOIN is important for database analysts and developers who work with relational databases to combine and analyze data from multiple sources.

1

Create the in_stock table and insert data

Create a table called in_stock with columns book_title (text) and quantity_in_stock (integer). Then insert these rows exactly: ('The Hobbit', 5), ('1984', 8), ('Dune', 3).

SQL

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

Need a hint?

Use CREATE TABLE to make the table, then INSERT INTO with the exact rows.

2

Create the ordered table and insert data

Create a table called ordered with columns book_title (text) and quantity_ordered (integer). Then insert these rows exactly: ('Dune', 7), ('The Catcher in the Rye', 4), ('1984', 2).

SQL

CREATE TABLE in_stock (
    book_title TEXT,
    quantity_in_stock INTEGER
);

INSERT INTO in_stock (book_title, quantity_in_stock) VALUES
('The Hobbit', 5),
('1984', 8),
('Dune', 3);

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

Need a hint?

Use CREATE TABLE and INSERT INTO like before, but for the ordered table.

3

Write the FULL OUTER JOIN query

Write a SELECT query that uses FULL OUTER JOIN to join in_stock and ordered on book_title. Select book_title, quantity_in_stock, and quantity_ordered.

SQL

CREATE TABLE in_stock (
    book_title TEXT,
    quantity_in_stock INTEGER
);

INSERT INTO in_stock (book_title, quantity_in_stock) VALUES
('The Hobbit', 5),
('1984', 8),
('Dune', 3);

CREATE TABLE ordered (
    book_title TEXT,
    quantity_ordered INTEGER
);

INSERT INTO ordered (book_title, quantity_ordered) VALUES
('Dune', 7),
('The Catcher in the Rye', 4),
('1984', 2);

-- Write the FULL OUTER JOIN query below
-- Your code here

Need a hint?

Use FULL OUTER JOIN to combine all rows from both tables. Use COALESCE to show the book title from either table.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by book_title alphabetically.

SQL

CREATE TABLE in_stock (
    book_title TEXT,
    quantity_in_stock INTEGER
);

INSERT INTO in_stock (book_title, quantity_in_stock) VALUES
('The Hobbit', 5),
('1984', 8),
('Dune', 3);

CREATE TABLE ordered (
    book_title TEXT,
    quantity_ordered INTEGER
);

INSERT INTO ordered (book_title, quantity_ordered) VALUES
('Dune', 7),
('The Catcher in the Rye', 4),
('1984', 2);

SELECT
    COALESCE(in_stock.book_title, ordered.book_title) AS book_title,
    quantity_in_stock,
    quantity_ordered
FROM in_stock
FULL OUTER JOIN ordered ON in_stock.book_title = ordered.book_title
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY book_title at the end of the query to sort the results alphabetically.