SQL Inner Join

INNER JOIN is used to combine rows from two or more tables based on a related column. It returns only the rows that have matching values in both tables, filtering out non-matching records. It is commonly used in relational databases and useful for working with related data.

Syntax:

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

Key Terms

• columns: specific columns we want to retrieve.
• table1 and table2: tables being joined.
• column_name: columns used for matching values.

Let's understand Inner Join better with examples.

Example of SQL INNER JOIN

To understand how INNER JOIN works, let’s first create two tables:

• A professor table that stores details about professors.
• A teacher table that contains information about courses taught by these professors.

Both tables are linked through a common column, ID in the professor table and prof_id in the teacher table.

professor Table

CREATE TABLE professor (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary INT );

INSERT INTO professor (ID, Name, Salary) VALUES
(1, Rohan Kumar, 57000),
(2, Hiroshi Tanaka, 45000),
(3, Maria Fernandez, 60000),
(4, Ahmed Hassan, 50000),
(5, Elena Petrova, 55000);

SELECT * FROM professor;

Output

teacher Table

CREATE TABLE teacher (
    course_id INT,
    prof_id INT,
    course_name VARCHAR(50) );

INSERT INTO teacher (course_id, prof_id, course_name) VALUES
(1, 1, 'English'),
(1, 3, 'Physics'),
(2, 4, 'Chemistry'),
(2, 5, 'Mathematics');

SELECT * FROM teacher;

Output

Now, if we want to join both tables to get combined information, we can use an INNER JOIN.

For example, let’s retrieve course_id, prof_id, along with professor’s Name and Salary. The join condition is that the ID column from the professor table must match the prof_id column from the teacher table.

Query:

SELECT teacher.course_id, teacher.prof_id, professor.Name, professor.Salary
FROM professor 
INNER JOIN teacher ON professor.ID = teacher.prof_id;

Output

Explanation: shows only professors who are assigned to a course. INNER JOIN matches rows where professor.ID equals teacher.prof_id, so only those professors appear. Professors without a course (like Hiroshi Tanaka) are excluded from the result.