MySQL INNER JOIN Keyword

  1. Overview
  2. MYSQL
  3. MySQL SQL
  4. MySQL INNER JOIN Keyword

The INNER JOIN keyword is used to select records that have matching values in both tables. This join returns only the rows where there is a match in the related columns between the two tables.

INNER JOIN Syntax

The basic syntax for an INNER JOIN in MySQL is as follows:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
  • table1 and table2 are the tables you want to join.
  • column_name is the column(s) that you want to use to match rows between the two tables.

Note:

  • The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in table1 (e.g., the Orders table) that do not have matching rows in table2 (e.g., the Customers table), those rows will not appear in the result.

Example: Joining Two Tables

Let's say you have two tables: Orders and Customers. You want to retrieve order details along with customer information for all the orders where there is a match in the CustomerID.

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
  • This query will return the OrderID, OrderDate, and CustomerName for each order where the CustomerID in the Orders table matches the CustomerID in the Customers table.

INNER JOIN with Three Tables

You can also join more than two tables using multiple INNER JOIN clauses. In this case, the result will only include rows where there is a match between all the tables involved.

Example: Joining Three Tables

If you want to select all orders along with customer and shipper details, you can use the following query:

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);
  • Explanation:
    • First, the query joins the Orders table with the Customers table using the CustomerID field.
    • Then, it joins the result of that with the Shippers table using the ShipperID field from the Orders table.
    • The result will include the OrderID, CustomerName, and ShipperName for each order where there are matching CustomerID and ShipperID values.

Key Takeaways

  • INNER JOIN returns only the rows where there is a match between the specified columns in both tables.
  • You can join multiple tables by chaining multiple INNER JOIN clauses.
  • The resulting dataset will only include rows with matches across all joined tables.

This join is useful when you want to retrieve data from multiple tables that are logically related and have matching values in certain columns.

Was this article helpful?