SQL INNER JOIN Example
Let’s look at some examples of the SQL INNER JOIN and understand it’s working.
First let’s create a demo database and table, on which we will perform the INNER JOIN.
CREATE DATABASE geeks;
USE geeks;
CREATE TABLE professor(
ID int,
Name varchar(20),
Salary int
);
CREATE TABLE teaches(
course_id int,
prof_id int,
course_name varchar(20)
);
INSERT INTO professor VALUES (1, 'Rohan', 57000);
INSERT INTO professor VALUES (2, 'Aryan', 45000);
INSERT INTO professor VALUES (3, 'Arpit', 60000);
INSERT INTO professor VALUES (4, 'Harsh', 50000);
INSERT INTO professor VALUES (5, 'Tara', 55000);
INSERT INTO teaches VALUES (1, 1, 'English');
INSERT INTO teaches VALUES (1, 3, 'Physics');
INSERT INTO teaches VALUES (2, 4, 'Chemistry');
INSERT INTO teaches VALUES (2, 5, 'Mathematics');
The resulting tables will be:
professor table:
| ID | Name | Salary |
|---|---|---|
| 1 | Rohan | 57000 |
| 2 | Aryan | 45000 |
| 3 | Arpit | 60000 |
| 4 | Harsh | 50000 |
| 5 | Tara | 55000 |
teaches Table
| course_id | prof_id | course_name |
|---|---|---|
| 1 | 1 | English |
| 1 | 3 | Physics |
| 2 | 4 | Chemistry |
| 2 | 5 | Mathematics |
INNER JOIN Query Example
In this example, we will use the INNER JOIN command on two tables.
Query
SELECT teaches.course_id, teaches.prof_id, professor.Name, professor.Salary
FROM professor INNER JOIN teaches ON professor.ID = teaches.prof_id;
Output :
Using the Inner Join we are able to combine the information in the two tables based on a condition and the tuples in the Cartesian product of the two tables that do not satisfy the required condition are not included in the resulting table.
| course_id | prof_id | Name | Salary |
|---|---|---|---|
| 1 | 1 | Rohan | 57000 |
| 1 | 3 | Arpit | 60000 |
| 2 | 4 | Harsh | 50000 |
| 2 | 5 | Tara | 55000 |
SQL Inner Join
SQL INNER JOIN combines two or more tables based on specified columns and retrieves records with matching values in the common columns.