PostgreSQL: SQL INNER JOIN Exercises (With Answers)

This guide is specifically for PostgreSQL syntax.

Learn SQL INNER JOIN with hands-on practice exercises. Each exercise includes the table schema, solution query, expected output, and detailed explanation.

What You'll Practice

These exercises cover INNER JOIN from basic to advanced concepts. Work through each problem to build your SQL skills.

Exercise 1

Question: Join employees with their departments to show employee name and department name.

Table Schema

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT
);

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

Solution

SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Expected Output

| employee_name | department_name | |---|---| | John | Sales | | Jane | Engineering |

Explanation

INNER JOIN returns only rows that have matching values in both tables. Use table aliases (e, d) for cleaner code.

Exercise 2

Question: Find all orders with their customer names and product names.

Table Schema

CREATE TABLE customers (id INT, name VARCHAR(100));
CREATE TABLE products (id INT, name VARCHAR(100), price DECIMAL);
CREATE TABLE orders (id INT, customer_id INT, product_id INT, quantity INT);

Solution

SELECT c.name AS customer, p.name AS product, o.quantity
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id;

Expected Output

| customer | product | quantity | |---|---|---| | John | Laptop | 2 | | Jane | Phone | 1 |

Explanation

You can chain multiple JOINs to connect several tables. Each JOIN adds one more table to the result.

Exercise 3

Question: List employees with their manager's name using a self-join.

Table Schema

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  manager_id INT
);

Solution

SELECT e.name AS employee, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

Expected Output

| employee | manager | |---|---| | Jane | John | | Bob | Jane |

Explanation

A self-join joins a table to itself. Use different aliases to distinguish between the two instances of the same table.

Common Mistakes to Avoid

• Forgetting to use proper syntax for INNER JOIN
• Not considering NULL values in comparisons
• Confusing similar operators or clauses

Related SQL Exercises

Continue practicing with these related topics:

PostgreSQL-Specific Notes

This page covers PostgreSQL syntax. Other databases may have different syntax for similar operations.