Open In App

SQL Inner Join

Last Updated : 10 May, 2021
Like Article

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.


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 –

    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

Like Article
Suggest improvement
Share your thoughts in the comments

Similar Reads