INNER JOIN

INNER JOINS

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.

• Combining records based on a related column.

• Returning only matching rows from both tables.

• Excluding non-matching.

• Ensuring accurate data relationships between tables

SYNTAX

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

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;

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;

et’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