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

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

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

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

Hint

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

Practice

1. Why are JOINs essential in PostgreSQL when working with multiple tables?

easy

A. They delete duplicate rows from a table.

B. They speed up the database server hardware.

C. They create new tables automatically.

D. They combine related data from different tables into one result.

Solution

1. Step 1: Understand the purpose of JOINs

   JOINs are used to combine rows from two or more tables based on a related column between them.

2. Step 2: Identify what JOINs do not do

   JOINs do not delete rows, create tables, or affect hardware speed.

3. Final Answer:

   They combine related data from different tables into one result. -> Option D

4. Quick Check:

   JOINs combine tables = B [OK]

Hint: Remember: JOINs merge related rows from tables [OK]

Common Mistakes:

• Thinking JOINs delete or create tables
• Confusing JOINs with database hardware functions
• Assuming JOINs work without related columns

2. Which of the following is the correct syntax to join two tables employees and departments on the column department_id?

easy

A. SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;

B. SELECT * FROM employees WHERE department_id = departments.department_id;

C. SELECT * FROM employees JOIN departments USING (department_id);

D. SELECT * FROM employees JOIN departments ON employees.id = departments.id;

Solution

1. Step 1: Check the JOIN syntax with ON clause

   The correct JOIN syntax uses ON to specify the matching columns: employees.department_id = departments.department_id.

2. Step 2: Verify other options

   SELECT * FROM employees WHERE department_id = departments.department_id; uses WHERE incorrectly for joining. SELECT * FROM employees JOIN departments USING (department_id); uses correct USING syntax with parentheses. SELECT * FROM employees JOIN departments ON employees.id = departments.id; joins on wrong columns (id instead of department_id).

3. Final Answer:

   SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id; -> Option A

4. Quick Check:

   JOIN with ON correct columns = C [OK]

Hint: Use ON with matching columns for JOINs [OK]

Common Mistakes:

• Using WHERE instead of ON for JOIN condition
• Joining on wrong columns
• Incorrect USING syntax without parentheses

3. Given two tables:
students(id, name)
grades(student_id, score)
What will be the result of this query?

SELECT students.name, grades.score FROM students JOIN grades ON students.id = grades.student_id;

medium

A. A list of scores without student names.

B. A list of student names with their corresponding scores.

C. An error because JOIN syntax is wrong.

D. A list of all students with NULL scores.

Solution

1. Step 1: Understand the JOIN condition

   The JOIN matches students.id with grades.student_id, linking each student to their scores.

2. Step 2: Predict the output columns

   The SELECT returns student names and their scores from matching rows only.

3. Final Answer:

   A list of student names with their corresponding scores. -> Option B

4. Quick Check:

   JOIN matches students to scores = D [OK]

Hint: JOIN returns matched rows combining columns [OK]

Common Mistakes:

• Assuming all students appear even without scores
• Thinking JOIN causes syntax error here
• Expecting only scores without names

4. Identify the error in this JOIN query:

SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.idd;

medium

A. Column name 'idd' does not exist in departments table.

B. Missing JOIN keyword.

C. Using table aliases incorrectly.

D. SELECT statement missing FROM clause.

Solution

1. Step 1: Check the JOIN condition columns

   The query uses 'd.idd' which is likely a typo; the correct column is probably 'd.id'.

2. Step 2: Confirm other syntax parts

   JOIN keyword and aliases are correctly used; FROM clause is present.

3. Final Answer:

   Column name 'idd' does not exist in departments table. -> Option A

4. Quick Check:

   Typo in column name = A [OK]

Hint: Check column names carefully in JOIN conditions [OK]

Common Mistakes:

• Typos in column names in ON clause
• Confusing table aliases usage
• Missing FROM clause (not in this case)

5. You have two tables:
orders(order_id, customer_id, amount)
customers(customer_id, name)
You want to list all customers and their total order amounts, including customers with no orders. Which query achieves this?

hard

A. SELECT c.name, SUM(o.amount) FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.name;

B. SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

C. SELECT c.name, SUM(o.amount) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

D. SELECT c.name, SUM(o.amount) FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;

Solution

1. Step 1: Understand the requirement for all customers

   To include customers with no orders, use LEFT JOIN from customers to orders.

2. Step 2: Use aggregation with GROUP BY

   SUM(o.amount) calculates total orders per customer; GROUP BY c.name groups results by customer.

3. Step 3: Check other options

   SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.customer_id = o.customer_id; excludes customers without orders (INNER JOIN). SELECT c.name, SUM(o.amount) FROM orders o LEFT JOIN customers c ON c.customer_id = o.customer_id GROUP BY c.name; reverses join direction, losing customers without orders. SELECT c.name, SUM(o.amount) FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name; uses RIGHT JOIN but groups by customer name from customers table, which may cause errors or exclude customers.

4. Final Answer:

   SELECT c.name, SUM(o.amount) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name; -> Option C

5. Quick Check:

   LEFT JOIN keeps all customers = A [OK]

Hint: Use LEFT JOIN to keep all from left table [OK]

Common Mistakes:

• Using INNER JOIN excludes customers without orders
• Confusing LEFT and RIGHT JOIN directions
• Forgetting GROUP BY when using aggregation