LEFT JOIN vs RIGHT JOIN decision in SQL - Hands-On Comparison

Understanding LEFT JOIN vs RIGHT JOIN in SQL

📖 Scenario: You work at a small bookstore. You have two tables: Books and Authors. Some books have authors listed, but some authors have not written any books yet. You want to learn how to use SQL joins to find all books with their authors, and also all authors with their books.

🎯 Goal: Build SQL queries step-by-step to understand the difference between LEFT JOIN and RIGHT JOIN. You will create tables, add data, and write queries that show all books with authors and all authors with books.

📋 What You'll Learn

Create two tables: Books and Authors

Insert specific data into both tables

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

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

💡 Why This Matters

🌍 Real World

Joins are used in databases to combine related data from different tables, like matching books to authors.

💼 Career

Understanding LEFT JOIN and RIGHT JOIN is essential for database querying in roles like data analyst, backend developer, and database administrator.

1

Create the Books and Authors tables

Write SQL commands to create a table called Books with columns BookID (integer, primary key) and Title (text). Also create a table called Authors with columns AuthorID (integer, primary key) and Name (text).

SQL

-- Create Books and Authors tables
-- Your code here

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Insert data into Books and Authors

Insert these rows into Books: (1, 'The Great Gatsby'), (2, '1984'), (4, 'Unknown Book'). Insert these rows into Authors: (1, 'F. Scott Fitzgerald'), (2, 'George Orwell'), (3, 'New Author').

SQL

CREATE TABLE Books (
  BookID INTEGER PRIMARY KEY,
  Title TEXT
);

CREATE TABLE Authors (
  AuthorID INTEGER PRIMARY KEY,
  Name TEXT
);

-- Insert data into Books and Authors
-- Your code here

Need a hint?

Use INSERT INTO with the exact values given.

3

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

Write a SQL query that selects Books.Title and Authors.Name using a LEFT JOIN on Books.BookID = Authors.AuthorID. This will show all books and their authors if available.

SQL

CREATE TABLE Books (
  BookID INTEGER PRIMARY KEY,
  Title TEXT
);

CREATE TABLE Authors (
  AuthorID INTEGER PRIMARY KEY,
  Name TEXT
);

INSERT INTO Books (BookID, Title) VALUES (1, 'The Great Gatsby'), (2, '1984'), (4, 'Unknown Book');
INSERT INTO Authors (AuthorID, Name) VALUES (1, 'F. Scott Fitzgerald'), (2, 'George Orwell'), (3, 'New Author');

-- Write LEFT JOIN query here
-- Your code here

Need a hint?

Use LEFT JOIN to get all books, even if no author matches.

4

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

Write a SQL query that selects Books.Title and Authors.Name using a RIGHT JOIN on Books.BookID = Authors.AuthorID. This will show all authors and their books if available.

SQL

CREATE TABLE Books (
  BookID INTEGER PRIMARY KEY,
  Title TEXT
);

CREATE TABLE Authors (
  AuthorID INTEGER PRIMARY KEY,
  Name TEXT
);

INSERT INTO Books (BookID, Title) VALUES (1, 'The Great Gatsby'), (2, '1984'), (4, 'Unknown Book');
INSERT INTO Authors (AuthorID, Name) VALUES (1, 'F. Scott Fitzgerald'), (2, 'George Orwell'), (3, 'New Author');

SELECT Books.Title, Authors.Name
FROM Books
LEFT JOIN Authors ON Books.BookID = Authors.AuthorID;

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

Need a hint?

Use RIGHT JOIN to get all authors, even if no book matches.