SQL Inner Join

Overview :
Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc. A join is a combination of a Cartesian product followed by a selection process. A join operation pairs two tuples from different relations if and only if a given join condition is satisfied. An inner join is the one in which only those tuples are included that satisfy some conditions. In this article, we will be using MySQL to demonstrate the working of SQL Inner Join.

Steps to implement the SQL Inner Join :
Here, we will discuss the implementation of SQL Inner Join as follows.

Step-1: Creating Database :
Here, we will create the database by using the following SQL query as follows.

CREATE DATABASE geeks;

Step-2: Using the Database :
Here, we will use the geeks database.

USE geeks;

Step-3: Adding Tables :
We will add 2 tables to the database as follows.

1. The first table will be the professor which will contain ID, the name of the professor, and salary.
2. The second table will be taught which will contain the ID of the course, professor’s ID, and name of the course.

Adding table professor –

CREATE TABLE professor(
    ID int,
    Name varchar(20),
    Salary int
);

Adding table teaches –

CREATE TABLE teaches(
    course_id int,
    prof_id int,
    course_name varchar(20)
);

Step-4: Description of the Tables :
We can get the description of the 2 tables using the following SQL command as follows.

DESCRIBE professor

Output :

Field	Type	Null	Key	Default	Extra
ID	int	YES		NULL
Name	varchar(20)	YES		NULL
Salary	int	YES		NULL

DESCRIBE teaches

Output :

Field	Type	Null	Key	Default	Extra
course_id	int	YES		NULL
prof_id	int	YES		NULL
course_name	varchar(20)	YES		NULL

Step-5: Inserting the rows :
Here, we will insert the rows in both tables one by one as follows.

Inserting rows inside professor table –

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

Output :

Inserting rows inside teaches table –

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

Output :

Step-6: Current State of the Tables :
Verifying the data in both tables as follows.

professor Table –

SELECT * FROM professor;

Output :

ID	Name	Salary
1	Rohan	57000
2	Aryan	45000
3	Arpit	60000
4	Harsh	50000
5	Tara	55000

teaches Table –

SELECT * FROM teaches;

Output :

course_id	prof_id	course_name
1	1	English
1	3	Physics
2	4	Chemistry
2	5	Mathematics

Step-7: INNER JOIN Query :

Syntax :

SELECT comma_separated_column_names
FROM table1 INNER JOIN table2 ON condition

Example –

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