Inner Join
An inner join returns all the rows in a table that have one or more matching rows in the other table. The unmatched rows are discarded. An inner join can be specified in both FROM clause or WHERE clause, but it is recommended to specify inner join in FROM clause.
SYNTAX:
SELECT T1.Column1, T2.Column2 …
FROM TABLE1 AS T1
INNER JOIN TABLE2 AS T2
ON T1.COL1 = T2.COL1
Inner Join
Table: Employee
EmployeeID FirstName LastName Title HireDate ManagerID
1 George Cruz CEO 12/8/2008 NULL
2 John Mathew Vice President 4/1/2009 1
3 Ethan William Director I 8/8/2009 2
4 Jacob Logan Director II 12/8/2011 3
5 Nathan Mateo Senior Manager 9/3/2009 4
6 Alice Charles Manager I 12/8/2012 5
7 John Robert Manager II 10/8/2011 6
8 Sophia James Manager III 5/8/2011 7
9 Kevin Chan Product Manager 7/8/2011 5
10 Susan Mortiz Solutions Arthitect 1/3/2012 6
11 Martha Miller Systems Arthitect 12/15/2012 10
Table: Location
LocationID LocationCode LocationName
1 L001 San Francisco
2 L002 New York
3 L003 Chicago
4 L004 Los Angeles
Example
Query :
SELECT E.EmployeeID, E.LastName, E.FirstName, L.*
FROM Employee AS E
INNER JOIN Location AS L ON E.EmployeeLocationID = L.LocationID
Output :
EmployeeID FirstName LastName LocationID LocationCode LocationName
1 George Cruz 1 L001 San Francisco
2 John Mathew 1 L001 San Francisco
3 Ethan William 2 L002 New York
5 Nathan Mateo 2 L002 New York
6 Alice Charles 1 L001 San Francisco
7 John Robert 2 L002 New York
9 Kevin Chan 1 L001 San Francisco
10 Susan Mortiz 1 L001 San Francisco
11 Martha Miller 1 L001 San Francisco
Description :
The above query with INNER JOIN compares each row in Employee table with each row in Location table to produce set of records which are common to both the tables.