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

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

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

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

Hint

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

Practice

1. What does a LEFT JOIN do in PostgreSQL?

easy

A. Returns all rows from the left table and matching rows from the right table, or NULL if no match.

B. Returns all rows from the right table and matching rows from the left table, or NULL if no match.

C. Returns only rows that have matching values in both tables.

D. Returns all rows from both tables, matching where possible.

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN returns all rows from the left table regardless of matches in the right table.

2. Step 2: Check what happens with unmatched rows

   If there is no matching row in the right table, the result includes NULLs for right table columns.

3. Final Answer:

   Returns all rows from the left table and matching rows from the right table, or NULL if no match. -> Option A

4. Quick Check:

   LEFT JOIN = all left rows + matched right rows [OK]

Hint: LEFT JOIN keeps all left rows, fills right with NULL if no match [OK]

Common Mistakes:

• Confusing LEFT JOIN with RIGHT JOIN
• Thinking LEFT JOIN returns only matched rows
• Assuming unmatched rows are dropped

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

easy

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

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

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

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

Solution

1. Step 1: Recall correct RIGHT JOIN syntax

   The correct syntax is: SELECT ... FROM left_table RIGHT JOIN right_table ON condition;

2. Step 2: Check each option

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; matches the correct syntax exactly. Options B, C, and D have syntax errors or misplaced keywords.

3. Final Answer:

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

4. Quick Check:

   RIGHT JOIN syntax = FROM left_table RIGHT JOIN right_table ON condition [OK]

Hint: RIGHT JOIN syntax: FROM left_table RIGHT JOIN right_table ON condition [OK]

Common Mistakes:

• Placing JOIN keyword incorrectly
• Using WHERE instead of ON for join condition
• Misordering table names

3. Given these tables:

employees
id | name
1 | Alice
2 | Bob
3 | Carol

departments
id | dept_name
2 | Sales
3 | HR
4 | IT

What is the result of this query?

SELECT employees.name, departments.dept_name FROM employees LEFT JOIN departments ON employees.id = departments.id ORDER BY employees.id;

medium

A. [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": "IT"}]

B. [{"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}, {"name": null, "dept_name": "IT"}]

C. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}]

D. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

Solution

1. Step 1: Understand LEFT JOIN with given tables

   LEFT JOIN keeps all employees. For each employee, it tries to find a department with matching id.

2. Step 2: Match rows and fill NULLs

   Alice (id=1) has no matching department, so dept_name is NULL. Bob (id=2) matches Sales. Carol (id=3) matches HR.

3. Final Answer:

   [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}] -> Option C

4. Quick Check:

   LEFT JOIN keeps all employees, unmatched dept_name is NULL [OK]

Hint: LEFT JOIN keeps all left rows; unmatched right columns are NULL [OK]

Common Mistakes:

• Assuming unmatched rows are excluded
• Mixing up LEFT and RIGHT JOIN results
• Ignoring NULL values for unmatched rows

4. You wrote this query but it gives an error:

SELECT * FROM employees RIGHT JOIN departments employees.id = departments.id;

What is the error and how to fix it?

medium

A. RIGHT JOIN is not supported; use LEFT JOIN instead

B. Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id

C. Table names are reversed; switch employees and departments

D. Use WHERE instead of ON for join condition

Solution

1. Step 1: Identify syntax error in JOIN clause

   The query is missing the ON keyword before the join condition, which is required syntax.

2. Step 2: Correct the syntax

   Add ON before employees.id = departments.id to fix the error.

3. Final Answer:

   Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id -> Option B

4. Quick Check:

   JOIN condition must use ON keyword [OK]

Hint: JOIN conditions always need ON keyword before condition [OK]

Common Mistakes:

• Omitting ON keyword
• Using WHERE instead of ON for join condition
• Confusing table order in JOIN

5. You want to list all departments and their employees, including departments with no employees. Which query achieves this?

hard

A. SELECT departments.dept_name, employees.name FROM employees RIGHT JOIN departments WHERE employees.id = departments.id;

B. SELECT departments.dept_name, employees.name FROM employees LEFT JOIN departments ON employees.id = departments.id;

C. SELECT departments.dept_name, employees.name FROM departments RIGHT JOIN employees ON departments.id = employees.id;

D. SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id;

Solution

1. Step 1: Identify which table to keep all rows from

   We want all departments, even those without employees, so departments is the left table in LEFT JOIN.

2. Step 2: Use LEFT JOIN from departments to employees

   LEFT JOIN keeps all departments and adds matching employees or NULL if none.

3. Final Answer:

   SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id; -> Option D

4. Quick Check:

   LEFT JOIN from departments keeps all departments [OK]

Hint: LEFT JOIN from main table keeps all its rows, unmatched get NULLs [OK]

Common Mistakes:

• Using employees as left table instead of departments
• Using RIGHT JOIN incorrectly
• Confusing which table to keep all rows from