INNER JOIN

Also referred to as an equi-join or simple join.
The use of Inner Joins is so common that whenever people talk of joins without full qualification they are always talking about inner joins.
An inner join is the most common and joins two tables with a common column name.
This is the most common type of join and only returns the rows that meet the exact criteria.
In other words you are matching every instance of the same value in another table.
This is typically the relationship between a primary key and a foreign key (but it doesn't have to be).
Any rows that do not satisfy the join condition are discarded.

You create an inner join by using the INNER JOIN and ON keywords.
The ON keyword is used to specify the condition

This works in both SQL Server and Access

SELECT       Employees.LastName, 
             Suppliers.CompanyName 
FROM         EMPLOYEES 
INNER JOIN   SUPPLIERS 
ON           Employees.City = Suppliers.City 

SELECT       ORDERS.* 
FROM         [CUSTOMERS] 
INNER JOIN   [ORDERS] 
ON           [CUSTOMERS].CustomerID = [ORDERS].CustomerID 
WHERE        [CUSTOMERS].CompanyName = 'Alfreds Futterkiste' 

ANSI Standard

SELECT       Orders.* 
FROM         Orders 
INNER JOIN   Customers 
ON           Customers.CustomerID = Orders.CustomerID 
WHERE        Customers.CompanyName = 'Alfreds Futterkiste' 

Notice that the other table is defined in the INNER JOIN clause.
Notice that the WHERE clause is only used to define selection criteria and not to perform the actual join.
The INNER keyword is not optional in Access.

Alternative

SELECT       Orders.* 
FROM         Orders, 
             Customers, 
WHERE        Customers.CustomerID = Orders.CustomerID 
AND          Customers.CompanyName = 'Alfreds Futterkiste' 

JOIN Alternative
can be specified just with the word JOIN
The word join is automatically replaced with "INNER JOIN" when used in SQL Server ? What about Access

WHERE Alternative
It is possible to perform an inner join by omitting the INNER JOIN and ON keywords and using a WHERE instead.

SELECT     [ORDERS].* 
FROM       [CUSTOMERS] 
           ,[ORDERS] 
WHERE     [CUSTOMERS].CustomerID = [ORDERS].CustomerID 
AND       [CUSTOMERS].CompanyName = 'Alfreds Futterkiste'