Combines records from two tables.




table [INNER] JOIN table ON condition


The INNER JOIN operator has these parts:





The names of the tables from which records are combined.


A Boolean expression restricting the records in the resulting table.




We can use an INNER JOIN operator in any FROM clause. For example, we can use INNER JOIN with the "Categories" and "Products" tables to select all products in each category. In contrast, to select all categories (even if some categories have no products) or all products (even if some do not belong to any category), we need to use a LEFT JOIN or RIGHT JOIN operator to create an outer join.


The following example shows how we could join the "Categories" and "Products" tables using the "Category ID" column:


SELECT [Category Name], [Product Name] FROM [Categories] INNER JOIN [Products] ON [Categories].[Category ID] = [Products].[Category ID];


We can use more than one column in a join condition.


We can nest JOIN statements using the following syntax:


table join (table join (table ...) ON condition) ON condition




This example uses the INNER JOIN operator to select all orders assigned to Andrew Fuller:


SELECT [Order ID], [Order Date] FROM [Orders] INNER JOIN [Employees] ON [Employees].[Employee ID] = [Orders].[Employee ID] AND [Employees].[First Name] = "Andrew" AND [Employees].[Last Name] = "Fuller";


The word "INNER" can be omitted, so the above query can be rewritten as:


SELECT [Order ID], [Order Date] FROM [Orders] JOIN [Employees] ON [Employees].[Employee ID] = [Orders].[Employee ID] AND [Employees].[First Name] = "Andrew" AND [Employees].[Last Name] = "Fuller";



This example uses nested INNER JOIN operators and GROUP BY clause to produce a list of employees and their total sales using "Employees," "Orders," and "Order Details" tables:


SELECT Sum([Unit Price] * [Quantity]) AS [Sales], ([First Name] & " " & [Last Name]) AS [Name] FROM [Employees] INNER JOIN ([Orders] INNER JOIN [Order Details] ON [Order Details].[Order ID] = [Orders].[Order ID]) ON [Orders].[Employee ID] = [Employees].[Employee ID] GROUP BY ([First Name] & " " & [Last Name]);