Why joins are essential in PostgreSQL - See It in Action

Why joins are essential

📖 Scenario: You work at a small bookstore that keeps two separate tables: one for books and one for authors. Each book has an author ID, but the author details are stored separately. You want to see the book titles along with their author names in one list.

🎯 Goal: Build a simple database query that joins the books and authors tables to show book titles with their author names. This will help you understand why joins are essential to combine related data from different tables.

📋 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 exact data into authors: (1, 'Jane Austen'), (2, 'Mark Twain'), (3, 'J.K. Rowling').

Insert exact data into books: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2), (103, 'Harry Potter and the Sorcerer''s Stone', 3).

Write a SELECT query that joins books and authors on author_id to show title and author_name.

💡 Why This Matters

🌍 Real World

In real businesses, data is often split into multiple tables to keep it organized and avoid repetition. Joins let you combine this data to get meaningful information.

💼 Career

Understanding joins is crucial for database jobs, data analysis, and backend development, as it helps you work with complex data stored in multiple tables.

1

Create the authors table and insert data

Create a table called authors with columns author_id as integer and author_name as text. Then insert these exact rows: (1, 'Jane Austen'), (2, 'Mark Twain'), and (3, 'J.K. Rowling').

PostgreSQL

-- Create authors table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE authors (author_id INTEGER, author_name TEXT); and then INSERT INTO authors with the given values.

2

Create the books table and insert data

Create a table called books with columns book_id as integer, title as text, and author_id as integer. Then insert these exact rows: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2), and (103, 'Harry Potter and the Sorcerer''s Stone', 3).

PostgreSQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Jane Austen'),
  (2, 'Mark Twain'),
  (3, 'J.K. Rowling');

-- Create books table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE books (book_id INTEGER, title TEXT, author_id INTEGER); and then INSERT INTO books with the given values.

3

Write the join query to combine books and authors

Write a SELECT query that joins the books and authors tables on the author_id column. Select the title from books and the author_name from authors.

PostgreSQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Jane Austen'),
  (2, 'Mark Twain'),
  (3, 'J.K. Rowling');

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

INSERT INTO books (book_id, title, author_id) VALUES
  (101, 'Pride and Prejudice', 1),
  (102, 'Adventures of Huckleberry Finn', 2),
  (103, 'Harry Potter and the Sorcerer''s Stone', 3);

-- Write the join query below
-- Your code here

Need a hint?

Use SELECT books.title, authors.author_name FROM books JOIN authors ON books.author_id = authors.author_id; to combine the tables.

4

Complete the query with an ORDER BY clause

Add an ORDER BY clause to the previous query to sort the results by author_name in ascending order.

PostgreSQL

CREATE TABLE authors (
  author_id INTEGER,
  author_name TEXT
);

INSERT INTO authors (author_id, author_name) VALUES
  (1, 'Jane Austen'),
  (2, 'Mark Twain'),
  (3, 'J.K. Rowling');

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

INSERT INTO books (book_id, title, author_id) VALUES
  (101, 'Pride and Prejudice', 1),
  (102, 'Adventures of Huckleberry Finn', 2),
  (103, 'Harry Potter and the Sorcerer''s Stone', 3);

SELECT books.title, authors.author_name
FROM books
JOIN authors ON books.author_id = authors.author_id
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Add ORDER BY authors.author_name ASC at the end of the query to sort by author name.