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.
Step-3: Adding Tables :
We will add 2 tables to the database as follows.
- The first table will be the professor which will contain ID, the name of the professor, and salary.
- 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.
Field Type Null Key Default Extra ID int YES NULL Name varchar(20) YES NULL Salary int YES NULL
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);
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');
Step-6: Current State of the Tables :
Verifying the data in both tables as follows.
professor Table –
SELECT * FROM professor;
ID Name Salary 1 Rohan 57000 2 Aryan 45000 3 Arpit 60000 4 Harsh 50000 5 Tara 55000
teaches Table –
SELECT * FROM teaches;
course_id prof_id course_name 1 1 English 1 3 Physics 2 4 Chemistry 2 5 Mathematics
Step-7: INNER JOIN Query :
SELECT comma_separated_column_names FROM table1 INNER JOIN table2 ON condition
SELECT teaches.course_id, teaches.prof_id, professor.Name, professor.Salary FROM professor INNER JOIN teaches ON professor.ID = teaches.prof_id;
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