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'


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext