INNER JOIN execution in PostgreSQL - Mini Project: Build & Apply

INNER JOIN Execution in PostgreSQL

📖 Scenario: You are managing a small online bookstore database. You have two tables: authors and books. Each book has an author_id that links it to an author. You want to find the names of authors along with the titles of their books.

🎯 Goal: Build a SQL query using INNER JOIN to combine the authors and books tables, showing each author's name with their book titles.

📋 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 the exact data provided into both tables.

Write a SQL query using INNER JOIN to select author_name and title from the joined tables.

💡 Why This Matters

🌍 Real World

INNER JOIN is used in databases to combine related data from different tables, such as linking customers to their orders or students to their classes.

💼 Career

Understanding INNER JOIN is essential for database querying roles, data analysis, and backend development where combining data from multiple tables is common.

1

Create the authors and books tables with data

Write SQL statements to create a table called authors with columns author_id (integer) and author_name (text). Then create a table called books with columns book_id (integer), title (text), and author_id (integer). Insert these exact rows into authors: (1, 'Jane Austen'), (2, 'Mark Twain'), (3, 'Charles Dickens'). Insert these exact rows into books: (101, 'Pride and Prejudice', 1), (102, 'Emma', 1), (103, 'Adventures of Huckleberry Finn', 2), (104, 'Oliver Twist', 3).

PostgreSQL

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

Hint

Use CREATE TABLE statements to define the tables. Use INSERT INTO with multiple rows to add the data.

2

Set up a variable for the join condition

Write a SQL comment that explains the join condition between authors.author_id and books.author_id. This will help you remember how the tables are connected.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

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

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Emma', 1),
(103, 'Adventures of Huckleberry Finn', 2),
(104, 'Oliver Twist', 3);

-- Your join condition comment here

Hint

Write a comment starting with -- that clearly states the join condition.

3

Write the INNER JOIN query

Write a SQL query that uses INNER JOIN to select author_name and title from the authors and books tables. Use the join condition authors.author_id = books.author_id.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

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

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Emma', 1),
(103, 'Adventures of Huckleberry Finn', 2),
(104, 'Oliver Twist', 3);

-- Write INNER JOIN query below

Hint

Use SELECT to choose columns, FROM for the first table, and INNER JOIN with ON for the join condition.

4

Complete the query with ordering

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

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER,
    author_name TEXT
);

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

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'Charles Dickens');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Emma', 1),
(103, 'Adventures of Huckleberry Finn', 2),
(104, 'Oliver Twist', 3);

-- Add ORDER BY clause below

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

Hint

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

Practice

1. What does an INNER JOIN do in PostgreSQL?

easy

A. Combines rows from two tables where matching column values exist in both tables.

B. Returns all rows from the first table, and matching rows from the second table.

C. Returns all rows from both tables, matching or not.

D. Deletes rows from both tables where values match.

Solution

1. Step 1: Understand INNER JOIN purpose

   INNER JOIN returns rows only when there is a match in both tables based on the join condition.

2. Step 2: Compare options with INNER JOIN behavior

   Combines rows from two tables where matching column values exist in both tables. correctly describes this behavior. Options B and C describe LEFT JOIN and FULL OUTER JOIN respectively, and D is unrelated.

3. Final Answer:

   Combines rows from two tables where matching column values exist in both tables. -> Option A

4. Quick Check:

   INNER JOIN = matched rows only [OK]

Hint: INNER JOIN returns only rows with matches in both tables [OK]

Common Mistakes:

• Confusing INNER JOIN with LEFT JOIN or FULL JOIN
• Thinking INNER JOIN returns unmatched rows
• Assuming INNER JOIN deletes data

2. Which of the following is the correct syntax for an INNER JOIN in PostgreSQL?

easy

A. SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

B. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id;

C. SELECT * FROM table1 INNER JOIN table2 USING (id);

D. SELECT * FROM table1 JOIN table2 ON table1.id == table2.id;

Solution

1. Step 1: Review INNER JOIN syntax

   The correct syntax uses INNER JOIN with ON clause specifying the join condition using a single equals sign (=).

2. Step 2: Check each option

   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; uses correct INNER JOIN syntax with ON and =. SELECT * FROM table1 INNER JOIN table2 WHERE table1.id = table2.id; missing ON or USING clause, syntax error. SELECT * FROM table1 INNER JOIN table2 USING (id); uses correct USING syntax with parentheses. SELECT * FROM table1 JOIN table2 ON table1.id == table2.id; uses double equals (==) which is invalid in SQL.

3. Final Answer:

   SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   INNER JOIN uses ON with = [OK]

Hint: Use ON with single = for INNER JOIN conditions [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Using double equals (==) instead of single =
• Confusing USING clause applicability

3. Given the tables:
employees(id, name, dept_id)
departments(id, dept_name)
What will this query return?

SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id;

medium

A. All employees, even if they have no matching department.

B. An error because of alias usage.

C. All departments with employee names, even if no employees exist.

D. All employees with their department names, only if dept_id matches a department id.

Solution

1. Step 1: Understand INNER JOIN effect on employees and departments

   The INNER JOIN returns rows where employees.dept_id matches departments.id, so only employees with a valid department appear.

2. Step 2: Analyze query output

   The query selects employee names and their department names for matching rows only. Employees without a matching department are excluded.

3. Final Answer:

   All employees with their department names, only if dept_id matches a department id. -> Option D

4. Quick Check:

   INNER JOIN filters unmatched rows [OK]

Hint: INNER JOIN shows only matching rows from both tables [OK]

Common Mistakes:

• Assuming unmatched employees appear
• Confusing aliases causing errors
• Thinking departments without employees appear

4. Identify the error in this INNER JOIN query:

SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id WHERE orders.date > '2023-01-01'

medium

A. Missing table alias causing ambiguity.

B. WHERE clause cannot be used with INNER JOIN.

C. No error; query is valid and will run correctly.

D. Incorrect join condition syntax; should use USING instead of ON.

Solution

1. Step 1: Check JOIN syntax and WHERE clause

   The INNER JOIN uses ON with a valid condition. The WHERE clause filters rows after the join, which is allowed.

2. Step 2: Verify if any syntax or semantic errors exist

   There is no ambiguity or syntax error. Aliases are optional. USING is an alternative but not required.

3. Final Answer:

   No error; query is valid and will run correctly. -> Option C

4. Quick Check:

   INNER JOIN with ON and WHERE is valid [OK]

Hint: WHERE filters after JOIN; no syntax error here [OK]

Common Mistakes:

• Thinking WHERE cannot be used with JOIN
• Confusing USING and ON as mandatory
• Assuming aliases are always required

5. You have two tables:
products(id, name)
sales(product_id, quantity)
Write a query to find product names with total quantity sold, but only for products that have sales.
Which query correctly uses INNER JOIN to achieve this?

hard

A. SELECT p.name, SUM(s.quantity) FROM sales s LEFT JOIN products p ON s.product_id = p.id GROUP BY p.name;

B. SELECT p.name, SUM(s.quantity) FROM products p INNER JOIN sales s ON p.id = s.product_id GROUP BY p.name;

C. SELECT p.name, SUM(s.quantity) FROM products p LEFT JOIN sales s ON p.id = s.product_id GROUP BY p.name;

D. SELECT p.name, SUM(s.quantity) FROM products p INNER JOIN sales s ON p.id = s.product_id WHERE p.name > 0 GROUP BY p.name;

Solution

1. Step 1: Understand requirement for products with sales only

   INNER JOIN returns only products that have matching sales records, excluding products without sales.

2. Step 2: Analyze each query

   SELECT p.name, SUM(s.quantity) FROM sales s LEFT JOIN products p ON s.product_id = p.id GROUP BY p.name; uses LEFT JOIN, includes unmatched sales (NULL p.name). SELECT p.name, SUM(s.quantity) FROM products p LEFT JOIN sales s ON p.id = s.product_id GROUP BY p.name; uses LEFT JOIN, includes products without sales. SELECT p.name, SUM(s.quantity) FROM products p INNER JOIN sales s ON p.id = s.product_id GROUP BY p.name; uses INNER JOIN correctly. SELECT p.name, SUM(s.quantity) FROM products p INNER JOIN sales s ON p.id = s.product_id WHERE p.name > 0 GROUP BY p.name; WHERE p.name > 0 causes type error.

3. Step 3: Choose best answer matching INNER JOIN usage

   SELECT p.name, SUM(s.quantity) FROM products p INNER JOIN sales s ON p.id = s.product_id GROUP BY p.name; is correct.

4. Final Answer:

   SELECT p.name, SUM(s.quantity) FROM products p INNER JOIN sales s ON p.id = s.product_id GROUP BY p.name; -> Option B

5. Quick Check:

   INNER JOIN + GROUP BY sums sales per product [OK]

Hint: Use INNER JOIN and GROUP BY to sum only matching sales [OK]

Common Mistakes:

• Using LEFT JOIN includes products without sales
• Forgetting GROUP BY causes errors
• Confusing table order in JOIN