MySQL INNER JOIN
The INNER JOIN clause returns only rows that have matching values in both tables.
INNER JOIN Syntax
SELECT table1.column1, table1.column2, ..., table2.column1, ...
FROM table1
INNER JOIN table2
ON table1.condition_column = table2.condition_column;
Note: The syntax combines two tables based on a related column, and the ON keyword is used to specify the matching condition.
MySQL INNER JOIN Example
Look at a product in the "Products" table:
| ProductID | ProductName | CategoryID | Price |
|---|---|---|---|
| 3 | Aniseed Syrup | 2 | 10.00 |
And look at a row in the "Categories" table:
| CategoryID | CategoryName | Description |
|---|---|---|
| 2 | Condiments | Sweet and savory sauces, relishes, spreads, and seasonings |
Here we see that the related column between the two tables above, is the "CategoryID" column.
Now we create an INNER JOIN on the "Products" table and the "Categories" table, via the CategoryID field:
Example
SELECT
Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID;
Note:
INNER JOINreturns only rows with a match in both tables. This means that if there is a product with no CategoryID, or with a CategoryID not present in the "Categories" table, that row will not be returned in the result.
JOIN Multiple Tables
You can join more than two tables by adding multiple INNER JOIN clauses in your query.
The following SQL selects all orders with customer and shipper information:
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;
