SQL INNER JOIN

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

Now, let’s examine a subset of the Products table:

ProductID	ProductName	CategoryID	Price
1	Chais	1	18
2	Chang	1	19
3	Aniseed Syrup	2	10

And an excerpt from the Categories table:

CategoryID	CategoryName	Description
1	Beverages	Soft drinks, coffees, teas, beers, and ales
2	Condiments	Sweet and savory sauces, relishes, spreads, and seasonings
3	Confections	Desserts, candies, and sweet breads

We’ll merge the Products table with the Categories table, linking them via the CategoryID field present in both tables.

Example

Utilize the INNER JOIN keyword to merge the Products and Categories tables:

SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

Note: The INNER JOIN keyword exclusively retrieves rows with a matching entry in both tables. This implies that if a product lacks a CategoryID or possesses a CategoryID not found in the Categories table, that particular record will not be included in the outcome.

Syntax

SELECT column_name(s)

FROM table1

INNER JOIN table2
 ON table1.column_name = table2.column_name;

Naming the Columns

It’s advisable to include the table name when referencing columns in the SQL statement.

Example

Explicitly state the table names when referencing them.

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

The example provided functions without specifying table names since none of the specified column names exist in both tables. However, attempting to include CategoryID in the SELECT statement will result in an error if the table name is not specified, as CategoryID is present in both tables.

JOIN or INNER JOIN

JOIN and INNER JOIN yield identical results.

INNER is the default join type for JOIN; therefore, when you use JOIN, the parser essentially executes INNER JOIN.

Example

JOIN is equivalent to INNER JOIN.

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID;

JOIN Three Tables

The subsequent SQL query retrieves all orders along with customer and shipper details:

Example

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);