'Inner Join' is a type of join that displays only the rows that have a match in both the joined tables.
That means that for every record returned in table A, there has to be a matching record in table B.
The Inner Join
The 'Inner Join 'is one of the most frequently used type of join. It is also known as EQUI JOIN.
The Where clause contains an '=' sign that acts as a comparison operator. You can even substitute the '=' sign with the keyword JOIN
instead to denote it as an Inner Join.
SQL 'Inner Join' Syntax
Select tableA.column1,tableA.column-2,tableA.column-3,...tableA.column-n,tableB.column1,
tableB.column2, .....tableB.columnn
from tableA, tableB
where tableA.columnname= tableB.columnname
Select tableA.column1,tableA.column-2,tableA.column-3,...tableA.column-n,tableB.column1,
tableB.column2,.....tableB.columnn
from table A
INNER JOIN tableB on tableA.columnname= tableB.columnname
The Inner Join Example
The
'Employees' tables contains the following columns and their respective data type as shown
below.
1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
New Orleans |
950.00 |
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
San Francisco |
800.00 |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
New York |
700.00 |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
Salt Lake City |
600.00 |
5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
New York |
500.00 |
6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
Houston |
400.00 |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
Long Island |
650.00 |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
Salt Lake City |
600.00 |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
San Francisco |
750.00 |
10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
Salt Lake City |
450.00 |
11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
Austin |
500.00 |
The
'Department' table contains their respective data type as shown
below.
1 |
Finance |
2 |
Development |
3 |
Testing |
4 |
Delivery |
5 |
Human Resources |
6 |
Administration |
Select A.EmployeeID,A.EmployeeName,A.DateOfBirth,A.DesignationID,A.City,
A.Salary,B.DeptID, B.DeptName
from Employees A
Inner Join Department B on A.DeptID=B.DeptID
1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
New Orleans |
950.00 |
1 |
Finance |
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
San Francisco |
800.00 |
2 |
Development |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
New York |
700.00 |
4 |
Delivery |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
Salt Lake City |
600.00 |
4 |
Delivery |
5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
New York |
500.00 |
3 |
Testing |
6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
Houston |
400.00 |
2 |
Development |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
Long Island |
650.00 |
6 |
Administration |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
Salt Lake City |
600.00 |
2 |
Development |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
San Francisco |
750.00 |
3 |
Testing |
10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
Salt Lake City |
450.00 |
2 |
Development |
Note: The Inner Join occurs on the common column DeptID which is present in both the tables. Here each record in the first table
is taken and checked against every record in the Department table to find a matching record. If so then only it is
selected and displayed.