SQL INNER JOIN

The SQL INNER JOIN clause is used to retrieve records that have matching values in both tables being joined.

When performing an INNER JOIN, only rows with matching values in both tables are returned in the result set. This join type is essential when working with relational databases to find data that exists in both tables based on a common column, such as an ID or a key field.

In this tutorial, we will go through SQL INNER JOIN, its syntax, and how to use this join in SQL statements, with the help of well detailed examples.

Syntax of SQL INNER JOIN

The basic syntax of the SQL INNER JOIN clause is:

</>
Copy
SELECT table1.column1, table2.column2, ...
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Each part of this syntax has a specific purpose:

  • SELECT: Specifies the columns to retrieve from each table.
  • FROM: Specifies the first table in the join operation.
  • INNER JOIN: Specifies the second table to join with the first.
  • ON: Specifies the condition for the join, which is the common column that both tables share.

Step-by-Step Examples with MySQL

We’ll go through examples demonstrating the INNER JOIN in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use sample students and grades tables with fields student_id, name, age, and subject.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

</>
Copy
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);

4. Create a grades table:

</>
Copy
CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    grade CHAR(1),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

5. Insert sample data into the students and grades tables:

</>
Copy
INSERT INTO students (name, age)
VALUES
('Alice', 14),
('Bob', 15),
('Charlie', 14);

INSERT INTO grades (student_id, subject, grade)
VALUES
(1, 'Math', 'A'),
(2, 'Math', 'B'),
(3, 'Science', 'A'),
(1, 'Science', 'B');