Let’s create two tables:

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT
);

Insert some sample data:

INSERT INTO departments (dept_name) VALUES
('HR'), ('IT'), ('Finance');

INSERT INTO employees (emp_name, dept_id) VALUES
('Ravi', 2),   -- IT
('Kavya', 1),  -- HR
('Rahul', 2),  -- IT
('Sneha', NULL); -- No department

INNER JOIN
An INNER JOIN retrieves only the rows that have matching values in both tables. It is useful when we want records that exist in both sides of the relationship. In the example below, the two tables are joined based on the common column dept_id.

The query checks each row from table1 against each row from table2 to find pairs that meet the join condition. Whenever the condition is satisfied, the matching rows are combined, and their column values are returned as a single result row.

Show employees with their department name:

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

Only employees who have a matching department (Ravi, Kavya, Rahul).