SQL INNER JOIN

Introduction

In this chapter, we will focus on the INNER JOIN operation in SQL. The INNER JOIN keyword selects records that have matching values in both tables involved in the join. This chapter will cover the definition, syntax, and provide examples to help you understand how to use the INNER JOIN effectively.

What is an INNER JOIN?

The INNER JOIN keyword selects records that have matching values in both tables. When two tables are joined with an INNER JOIN, only the rows that have matching values in both tables are included in the result set. If there is no match between the columns, the row will not be included in the result set.

Syntax for INNER JOIN

Basic Syntax

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

• columns: The columns you want to retrieve.
• table1: The first table you want to join.
• table2: The second table you want to join.
• table1.column = table2.column: The condition that specifies how the tables should be joined.

Step-by-Step Example

Sample Tables

First, let's create two sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', NULL, 50000);

Using INNER JOIN

To retrieve employees along with their department names:

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Output

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance

In this example, only the employees who have a department_id that matches a department_id in the departments table are included in the result set.

Filtering with INNER JOIN

You can also use additional filtering conditions with INNER JOIN.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
WHERE employees.salary > 55000;

Output

first_name	last_name	department_name
Sita	Patel	Finance
Priya	Sharma	Finance

In this example, only the employees with a salary greater than 55000 are included in the result set, along with their department names.

Multiple Table INNER JOIN

You can join more than two tables using INNER JOIN.

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO projects (project_id, project_name, department_id)
VALUES
(1, 'Project A', 1),
(2, 'Project B', 2),
(3, 'Project C', 3);

SELECT employees.first_name, employees.last_name, departments.department_name, projects.project_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
INNER JOIN projects ON departments.department_id = projects.department_id;

Output

first_name	last_name	department_name	project_name
Ramesh	Kumar	HR	Project A
Arjun	Singh	HR	Project A
Sita	Patel	Finance	Project B
Priya	Sharma	Finance	Project B

In this example, we join the employees, departments, and projects tables to retrieve employees along with their department names and project names.

Conclusion

The INNER JOIN is used for combining rows from two or more tables based on a related column. This chapter covered the basic syntax for INNER JOIN, provided examples to illustrate its use, and demonstrated how to filter and join multiple tables. Understanding how to use INNER JOIN effectively will greatly enhance your ability to query and analyze data across multiple tables in a relational database. In the next chapter, we will explore LEFT JOIN with examples.