SQL-INNER JOIN

<< Previous - SQL Join

Next - SQL LEFT OUTER Join >>

• The inner join returns all records that are common to both tables.
• The keyword INNER JOIN is used to select the row from both tables, based on matching column fields in both tables.
• Both JOIN and INNER JOIN keywords are the same. We can use any of them, but the result set will be the same.

Below is a diagram to illustrate the inner join.

Here,Consider the two tables(Left and Right)in which you can select the records. In the above picture, the shaded area shows the selected rows from both tables, that have a matching column value.

SQL INNER JOIN - Syntax

SELECT Table1.column-name1 Table1.column-name2  Table2.column-name 2......
FROM Table-name1
INNER JOIN Table-name2
ON Table1.column =  Table2.column;

Example with INNER JOIN

Consider the demo tables that are given below:

1. Student_Personal Table:

   
   +--------+---------+------+-----------+
   | RollNo | Name    | Age  | City      |
   +--------+---------+------+-----------+
   |      1 | Aruna   |   18 | Chennai   |
   |      2 | Varun   |   19 | Bangalore |
   |      3 | Ara     |   19 | Kerala    |
   |      4 | Markdin |   18 | Mumbai    |
   |      5 | Kannan  |   20 | Kerala    |
   |      6 | Kanika  |   18 | Chennai   |
   |      7 | Jose    |   19 | Kerala    |
   |     10 | Sreeja  |   20 | Bangalore |
   +--------+---------+------+-----------+
   

2. Student_ID Table:

   
   +--------+-------+---------+------+
   | RollNo | ID_NO | Name    | Dept |
   +--------+-------+---------+------+
   |      1 |   101 | Arun    | CSE  |
   |      2 |   102 | Varun   | IT   |
   |      3 |   103 | Ara     | ECE  |
   |      4 |   104 | Markdin | ECE  |
   |      5 |   105 | Kannan  | IT   |
   |      6 |   106 | Aruna   | CSE  |
   |      7 |   107 | Jose    | ECE  |
   |      8 |   108 | Jithu   | CSE  |
   |      9 |   109 | Sai     | ECE  |
   +--------+-------+---------+------+
   

   Following is the INNER JOIN query statement used to join these two tables:

   
   mysql> SELECT Student_Personal.Rollno,Student_Personal.Name,Student_ID.ID_NO,Student_ID.Dept
       -> FROM Student_Personal
       -> INNER JOIN Student_ID
       -> ON student_Personal.RollNo = Student_ID.RollNo;
   

   When you execute this above statement, you would get the following result-set

   
   +--------+---------+-------+------+
   | Rollno | Name    | ID_NO | Dept |
   +--------+---------+-------+------+
   |      1 | Aruna   |   101 | CSE  |
   |      2 | Varun   |   102 | IT   |
   |      3 | Ara     |   103 | ECE  |
   |      4 | Markdin |   104 | ECE  |
   |      5 | Kannan  |   105 | IT   |
   |      6 | Kanika  |   106 | CSE  |
   |      7 | Jose    |   107 | ECE  |
   +--------+---------+-------+------+
   7 rows in set (0.00 sec)
   

   << Previous -SQL Join

   Next - SQL LEFT OUTER Join>>

   
   
   
   

   ---