SQL Inner Join
INNER JOIN
The records with matching values in both tables are selected by the INNER JOIN keyword.
Now let’s examine a few items from the Products table:
| ProductID | ProductName | CategoryID | Price |
|---|---|---|---|
| 1 | Chais | 1 | 18 |
| 2 | Chang | 1 | 19 |
| 3 | Aniseed Syrup | 2 | 10 |
And a selection of 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 will join the Products table with the Categories table, by using the CategoryID field from both tables:
Example
Join Products and Categories with the INNER JOIN keyword:
SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;
Note: that only rows that match in both tables are returned by the INNER JOIN keyword. This implies that a record would not be returned in the result if you had a product with no CategoryID or a CategoryID that was not found in the Categories table.
Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Naming the Columns
Adding the table name when defining columns in the SQL statement is a best practice.
Example
Specify the table names:
SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;
Since none of the given column names are present in either table, the example above functions without table names. Because CategoryID is present in both tables, if you try to include it in the SELECT statement without specifying the table name, you will receive an error.
JOIN or INNER JOIN
The outcome of JOIN and INNER JOIN is the same.
Since INNER is the default join type for JOIN, the parser really writes INNER JOIN when you write JOIN.
Example
JOIN is the same as INNER JOIN:
SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID;
JOIN Three Tables
The following SQL statement selects all orders with customer and shipper information:
Here is the Shippers table:
| ShipperID | ShipperName | Phone |
|---|---|---|
| 1 | Speedy Express | (503) 555-9831 |
| 2 | United Package | (503) 555-3199 |
| 3 | Federal Shipping | (503) 555-9931 |
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);
