INNER JOIN execution in PostgreSQL

Introduction

INNER JOIN helps combine rows from two tables when they have matching values in a related column.

You want to see orders along with customer details where the order exists.

You need to find employees and their departments only if the employee is assigned to a department.

You want to list products and their suppliers but only for products that have suppliers.

You want to combine student records with their enrolled courses only if enrollment exists.

Syntax

PostgreSQL

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

INNER JOIN returns only rows where the join condition matches in both tables.

If no match is found, the row is not included in the result.

Examples

This gets employee names with their department names only if the employee belongs to a department.

PostgreSQL

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

This lists order IDs with customer names only for orders that have a matching customer.

PostgreSQL

SELECT orders.id, customers.name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id;

Sample Program

This example creates two tables, inserts data, and uses INNER JOIN to show orders with matching customers. The order with customer_id 3 is excluded because no such customer exists.

PostgreSQL

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT,
  product VARCHAR(50)
);

INSERT INTO customers (name) VALUES ('Alice'), ('Bob');
INSERT INTO orders (customer_id, product) VALUES (1, 'Book'), (2, 'Pen'), (3, 'Notebook');

SELECT orders.id AS order_id, customers.name AS customer_name, orders.product
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id
ORDER BY orders.id;

Important Notes

INNER JOIN only shows rows where both tables have matching values in the join column.

If you want to keep all rows from one table even if no match exists, consider LEFT JOIN instead.

Always specify the join condition with ON to avoid a large, unintended result.

Summary

INNER JOIN combines rows from two tables based on matching column values.

Only rows with matches in both tables appear in the result.

Use INNER JOIN when you want related data from both tables and no unmatched rows.