SQL INNER JOIN – Syntax and Examples

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:

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:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

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

4. Create a grades table:

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:

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');

---

Examples: Using INNER JOIN in Queries

Now, let’s explore different scenarios of using SQL INNER JOIN with these tables.

---

1. Retrieving Students with Their Grades

To retrieve the names and grades of students along with the subjects in which they have grades:

SELECT students.name, grades.subject, grades.grade
FROM students
INNER JOIN grades
ON students.student_id = grades.student_id;

This query returns a list of students and their grades, showing only those students with records in both the students and grades tables.

---

2. Using INNER JOIN with Additional Conditions

To find students who have scored a grade of “A” in any subject:

SELECT students.name, grades.subject, grades.grade
FROM students
INNER JOIN grades
ON students.student_id = grades.student_id
WHERE grades.grade = 'A';

This query returns only students who have a grade of “A”, showing their names and the subject they scored in.

---

3. Joining More Than Two Tables

If we add an additional classes table with details about each class, we can join three tables using INNER JOIN:

CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    subject VARCHAR(50),
    teacher VARCHAR(50)
);

INSERT INTO classes (subject, teacher)
VALUES
('Math', 'Mr. Smith'),
('Science', 'Ms. Johnson');

SELECT students.name, grades.grade, classes.teacher
FROM students
INNER JOIN grades ON students.student_id = grades.student_id
INNER JOIN classes ON grades.subject = classes.subject;

This query joins three tables to display students’ names, grades, and the teacher for each subject where they have a grade.

---

FAQs for SQL INNER JOIN

1. What does the SQL INNER JOIN do?

The INNER JOIN returns rows with matching values in both tables, excluding rows where there is no match.

2. Can INNER JOIN be used with multiple tables?

Yes, you can use INNER JOIN to join more than two tables by specifying additional JOIN clauses and conditions.

3. How is INNER JOIN different from LEFT JOIN?

INNER JOIN only returns rows with matches in both tables, while LEFT JOIN includes all rows from the left table, with NULL for non-matching rows in the right table.

4. Can INNER JOIN be used with conditions?

Yes, you can use WHERE conditions with INNER JOIN to filter the results further.

5. Is INNER JOIN the default join type in SQL?

Yes, in many SQL databases, if you specify just JOIN, it is treated as an INNER JOIN by default.