Why advanced joins matter in SQL - See It in Action

Why Advanced Joins Matter

📖 Scenario: You work at a small bookstore that keeps track of books and authors in separate tables. You want to learn how to combine information from both tables to see which author wrote which book.

🎯 Goal: Build SQL queries step-by-step to join the books and authors tables and understand why advanced joins are important to get complete information.

📋 What You'll Learn

Create a books table with columns book_id, title, and author_id

Create an authors table with columns author_id and author_name

Write a query to join books and authors using INNER JOIN

Write a query to join books and authors using LEFT JOIN

Write a query to join books and authors using FULL OUTER JOIN

💡 Why This Matters

🌍 Real World

Bookstores, libraries, and many businesses store related data in separate tables. Joining tables helps combine this data to answer important questions.

💼 Career

Understanding joins is essential for database jobs, data analysis, and backend development where combining data from multiple sources is common.

1

Create the books and authors tables

Write SQL statements to create a table called books with columns book_id (integer), title (text), and author_id (integer). Then create a table called authors with columns author_id (integer) and author_name (text).

SQL

-- Create the books table
-- Create the authors table

Need a hint?

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

2

Insert sample data into books and authors

Insert these exact rows into books: (1, 'The Great Gatsby', 101), (2, '1984', 102), (3, 'Unknown Book', NULL). Insert these exact rows into authors: (101, 'F. Scott Fitzgerald'), (102, 'George Orwell'), (103, 'J.K. Rowling').

SQL

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

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

-- Insert data into books
-- Insert data into authors

Need a hint?

Use INSERT INTO statements with the exact values given.

3

Write an INNER JOIN query to find books with their authors

Write a SQL query that uses INNER JOIN to select title from books and author_name from authors where books.author_id = authors.author_id.

SQL

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

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO books VALUES (1, 'The Great Gatsby', 101);
INSERT INTO books VALUES (2, '1984', 102);
INSERT INTO books VALUES (3, 'Unknown Book', NULL);

INSERT INTO authors VALUES (101, 'F. Scott Fitzgerald');
INSERT INTO authors VALUES (102, 'George Orwell');
INSERT INTO authors VALUES (103, 'J.K. Rowling');

-- Write INNER JOIN query here

Need a hint?

Use INNER JOIN to combine rows where author IDs match.

4

Write LEFT JOIN and FULL OUTER JOIN queries to see all books and authors

Write two SQL queries: one using LEFT JOIN to select all books with their authors (including books without authors), and one using FULL OUTER JOIN to select all books and all authors (including authors without books). Use books.title and authors.author_name in both queries with the join condition books.author_id = authors.author_id.

SQL

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

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO books VALUES (1, 'The Great Gatsby', 101);
INSERT INTO books VALUES (2, '1984', 102);
INSERT INTO books VALUES (3, 'Unknown Book', NULL);

INSERT INTO authors VALUES (101, 'F. Scott Fitzgerald');
INSERT INTO authors VALUES (102, 'George Orwell');
INSERT INTO authors VALUES (103, 'J.K. Rowling');

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

-- Write LEFT JOIN query here

-- Write FULL OUTER JOIN query here

Need a hint?

Use LEFT JOIN to include all books even if no author matches, and FULL OUTER JOIN to include all books and all authors.