RIGHT JOIN execution behavior in SQL - Mini Project: Build & Apply

Understanding RIGHT JOIN Execution Behavior in SQL

📖 Scenario: You work at a small bookstore that keeps two tables: one for books and one for authors. Some books might not have an author listed yet. You want to see all authors and any books they wrote, including authors who have no books listed.

🎯 Goal: Build a SQL query using RIGHT JOIN to list all authors and their books, showing authors even if they have no books.

📋 What You'll Learn

Create a table called books with columns book_id (integer) and author_id (integer), and title (text).

Create a table called authors with columns author_id (integer) and name (text).

Insert the exact data provided into both tables.

Write a RIGHT JOIN query joining books to authors on author_id.

Select authors.name and books.title in the output.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use joins to combine related data from different tables, such as authors and books.

💼 Career

Understanding RIGHT JOIN is important for database querying roles, data analysis, and backend development.

1

Create the books and authors tables with data

Create a table called books with columns book_id (integer), author_id (integer), and title (text). Then create a table called authors with columns author_id (integer) and name (text). Insert these rows into books: (1, 101, 'Learn SQL'), (2, 102, 'Advanced SQL'). Insert these rows into authors: (101, 'Alice'), (102, 'Bob'), (103, 'Charlie').

SQL

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

Need a hint?

Use CREATE TABLE statements for both tables and INSERT INTO for each row exactly as given.

2

Set up the join condition variable

Create a variable or comment named join_condition that holds the join condition books.author_id = authors.author_id to use in the RIGHT JOIN.

SQL

CREATE TABLE books (book_id INTEGER, author_id INTEGER, title TEXT);
CREATE TABLE authors (author_id INTEGER, name TEXT);
INSERT INTO books VALUES (1, 101, 'Learn SQL');
INSERT INTO books VALUES (2, 102, 'Advanced SQL');
INSERT INTO authors VALUES (101, 'Alice');
INSERT INTO authors VALUES (102, 'Bob');
INSERT INTO authors VALUES (103, 'Charlie');
-- Define join_condition as books.author_id = authors.author_id

Need a hint?

Use a comment or variable named join_condition exactly with the text books.author_id = authors.author_id.

3

Write the RIGHT JOIN query using the join condition

Write a SQL query that selects authors.name and books.title from books RIGHT JOIN authors ON the join condition books.author_id = authors.author_id.

SQL

CREATE TABLE books (book_id INTEGER, author_id INTEGER, title TEXT);
CREATE TABLE authors (author_id INTEGER, name TEXT);
INSERT INTO books VALUES (1, 101, 'Learn SQL');
INSERT INTO books VALUES (2, 102, 'Advanced SQL');
INSERT INTO authors VALUES (101, 'Alice');
INSERT INTO authors VALUES (102, 'Bob');
INSERT INTO authors VALUES (103, 'Charlie');
-- join_condition: books.author_id = authors.author_id
SELECT authors.name, books.title FROM books RIGHT JOIN authors ON books.author_id = authors.author_id

Need a hint?

Use RIGHT JOIN with the exact join condition and select the columns as specified.

4

Complete the query with ordering

Add an ORDER BY authors.name clause at the end of the query to sort the results by author name.

SQL

CREATE TABLE books (book_id INTEGER, author_id INTEGER, title TEXT);
CREATE TABLE authors (author_id INTEGER, name TEXT);
INSERT INTO books VALUES (1, 101, 'Learn SQL');
INSERT INTO books VALUES (2, 102, 'Advanced SQL');
INSERT INTO authors VALUES (101, 'Alice');
INSERT INTO authors VALUES (102, 'Bob');
INSERT INTO authors VALUES (103, 'Charlie');
-- join_condition: books.author_id = authors.author_id
SELECT authors.name, books.title FROM books RIGHT JOIN authors ON books.author_id = authors.author_id
-- Add ORDER BY authors.name

Need a hint?

Add ORDER BY authors.name exactly at the end of the query.