LEFT JOIN and RIGHT JOIN in PostgreSQL - Mini Project: Build & Apply

Using LEFT JOIN and RIGHT JOIN in PostgreSQL

📖 Scenario: You work for a small bookstore that keeps two tables: one for authors and one for books. Some authors may not have published books yet, and some books may not have a listed author.

🎯 Goal: Build SQL queries using LEFT JOIN and RIGHT JOIN to combine the authors and books tables. This will help you see all authors with their books, and all books with their authors, even if some data is missing.

📋 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 given.

Write a LEFT JOIN query to list all authors and their books (if any).

Write a RIGHT JOIN query to list all books and their authors (if any).

💡 Why This Matters

🌍 Real World

Bookstores and libraries often keep separate tables for authors and books. Using LEFT and RIGHT JOINs helps show complete lists even when some data is missing.

💼 Career

Understanding JOINs is essential for database querying jobs, data analysis, and backend development where combining related data is common.

1

Create the authors and books tables with data

Write SQL commands to create a table called authors with columns author_id (integer) and author_name (text). Then create a table called books with columns book_id (integer), title (text), and author_id (integer). Finally, insert these exact rows into authors: (1, 'Alice Walker'), (2, 'Mark Twain'), (3, 'Jane Austen'). Insert these exact rows into books: (101, 'The Color Purple', 1), (102, 'Adventures of Huckleberry Finn', 2), (103, 'Emma', 3), (104, 'Unknown Book', NULL).

PostgreSQL

-- Create authors table
-- Create books table
-- Insert data into authors
-- Insert data into books

Need a hint?

Use CREATE TABLE statements for both tables. Use INSERT INTO with exact values for each row.

2

Set up a variable for the join condition

Create a SQL variable or comment named join_condition that represents the condition authors.author_id = books.author_id. This will be used in the JOIN queries.

PostgreSQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

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

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Alice Walker'),
  (2, 'Mark Twain'),
  (3, 'Jane Austen');

INSERT INTO books (book_id, title, author_id) VALUES
  (101, 'The Color Purple', 1),
  (102, 'Adventures of Huckleberry Finn', 2),
  (103, 'Emma', 3),
  (104, 'Unknown Book', NULL);

-- Define join_condition as authors.author_id = books.author_id

Need a hint?

Since PostgreSQL does not support variables in plain SQL scripts, use a comment to define the join condition for clarity.

3

Write a LEFT JOIN query to list all authors and their books

Write a SQL query that selects authors.author_name and books.title using a LEFT JOIN on the authors and books tables. Use the join condition authors.author_id = books.author_id. This query should show all authors, even if they have no books.

PostgreSQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

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

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Alice Walker'),
  (2, 'Mark Twain'),
  (3, 'Jane Austen');

INSERT INTO books (book_id, title, author_id) VALUES
  (101, 'The Color Purple', 1),
  (102, 'Adventures of Huckleberry Finn', 2),
  (103, 'Emma', 3),
  (104, 'Unknown Book', NULL);

-- join_condition: authors.author_id = books.author_id

SELECT authors.author_name, books.title
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id;
-- Your code here

Need a hint?

Use LEFT JOIN to keep all authors, even those without books.

4

Write a RIGHT JOIN query to list all books and their authors

Write a SQL query that selects books.title and authors.author_name using a RIGHT JOIN on the authors and books tables. Use the join condition authors.author_id = books.author_id. This query should show all books, even if they have no author.

PostgreSQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

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

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Alice Walker'),
  (2, 'Mark Twain'),
  (3, 'Jane Austen');

INSERT INTO books (book_id, title, author_id) VALUES
  (101, 'The Color Purple', 1),
  (102, 'Adventures of Huckleberry Finn', 2),
  (103, 'Emma', 3),
  (104, 'Unknown Book', NULL);

-- join_condition: authors.author_id = books.author_id

SELECT authors.author_name, books.title
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id;

-- Write RIGHT JOIN query below
-- Your code here

Need a hint?

Use RIGHT JOIN to keep all books, even those without authors.