JetSQL » Operators » INNER JOIN
- Syntax:
-
SELECT * | fieldlist FROM table1
INNER JOIN table2 ON table1.field1 compoperator table2.field2 - fieldlist
- The list of fields that are to be retrieved from the table.
- table1
- The name of the first table from which information is to be retrieved.
- table2
- The name of the second table from which information is to be retrieved.
- field1
- The field from the first table that is being compared to field2 from the second table.
- compoperator
- A comparison operator such as =, >, <, etc.
- field2
- The field from the second table that is being compared to field1 from the first table.
The INNER JOIN operator can be used in any FROM clause to combine records from two tables.
It is, in fact, the most common type of join. There must be a matching value in a field common to both tables. An INNER JOIN cannot be nested inside a LEFT JOIN or RIGHT JOIN.
Examples
Code:
SELECT Employee.Username
FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
WHERE Employee.City = 'Boston'
AND Project.ProjectName = 'Hardwork'
Output:
Username Jack Smith Herman Allen Jill Swafford Bob Thornton
(4 row(s) affected)
Explanation:
The preceding example returns a list of all employees who live in Boston and who are working on the Hardwork project.
Note: You can join any two numeric fields as long as they are of like type (such as AutoNumber and Long). However, with non-numeric data, the fields must be of the same type and contain the same kind of data, though they can have different names.
Language(s): MS SQL Server
Code:
SELECT Employee.Username, Project.ProjectName
FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
Output:
Username ProjectName Jack Smith Hardwork Herman Allen Hardwork Jill Swafford Hardwork Bob Thornton Hardwork Mike Sosebee Grindstone Jill Swafford Grindstone Zack Womble Hardwork
(7 row(s) affected)
Explanation:
With the INNER JOIN operator, any relational comparison operator can be used in the ON clause: =, <, >, <=, >=, or <>. The above example returns all cases where the value in the 'EmployeeID' field of the 'Employee' table matches the 'EmployeeID' field of the 'Project' table (i.e. it returns the names of those employees working on each of the projects).
Language(s): MS SQL Server
Code:
SELECT Employee.Username, Project.ProjectName
FROM Employee INNER JOIN Project
ON Employee.EmployeeID <> Project.EmployeeID
Output:
Username ProjectName Jack Smith Grindstone Herman Allen Grindstone Bob Thornton Grindstone Mike Sosebee Hardwork Zack Womble Grindstone
(5 row(s) affected)
Explanation:
This example returns all employees not working on each project.
Language(s): MS SQL Server
Code:
SELECT Employee.Username, Project.ProjectName, Project.Location
FROM Employee INNER JOIN Project
ON (Employee.EmployeeID = Project.EmployeeID)
AND (Employee.City = Project.Location)
Output:
Username ProjectName Location Mike Sosebee Grindstone Boston Jill Swafford Grindstone Boston Zack Womble Hardwork Lexington
(3 row(s) affected)
Explanation:
You can also link several clauses in an INNER JOIN statement. The preceding example returns all employees working on each project who live in the same city as where the project is taking place.
Language(s): MS SQL Server
Code:
SELECT Songs.SongName, Singers.Name, Duos.DuoName
FROM Songs INNER JOIN (Singers INNER JOIN Duos
ON (Singers.Name = Duos.Member1)
OR (Singers.Name = Duos.Member2))
ON Songs.Musician = Singers.Name
Output:
SongName Name DuoName I Got You Babe Sonny Bono Sonny and Cher I Got You Babe Cher Sonny and Cher Baby Don't Go Sonny Bono Sonny and Cher Baby Don't Go Cher Sonny and Cher
A Little Bit Country, A Little Bit Rock and Roll
Donnie Osmond
The Osmonds
A Little Bit Country, A Little Bit Rock and Roll
Marie Osmond
The Osmonds
This Country's Rockin' Wynonna Judd The Judds This Country's Rockin' Naomi Judd The Judds Love Letters in the Sand Pat Boone Pat and Shirley Boone
(9 row(s) affected)
Explanation:
It is also possible to nest statements as in this example which returns all songs recorded by musicians who are members of duos.
Language(s): MS SQL Server
Code:
SELECT Songs.SongName, Singers.Name, Duos.DuoName
FROM Songs, Singers, Duos
WHERE ((Singers.Name = Duos.Member1)
OR (Singers.Name = Duos.Member2))
AND (Songs.Musician = Singers.Name)
Output:
SongName Name DuoName I Got You Babe Sonny Bono Sonny and Cher I Got You Babe Cher Sonny and Cher Baby Don't Go Sonny Bono Sonny and Cher Baby Don't Go Cher Sonny and Cher
A Little Bit Country, A Little Bit Rock and Roll
Donnie Osmond
The Osmonds
A Little Bit Country, A Little Bit Rock and Roll
Marie Osmond
The Osmonds
This Country's Rockin' Wynonna Judd The Judds This Country's Rockin' Naomi Judd The Judds Love Letters in the Sand Pat Boone Pat and Shirley Boone
(9 row(s) affected)
Explanation:
An inner join can also be achieved by using the WHERE clause. This query returns the same set of records as the previous example.
Language(s): MS SQL Server
See Also:
