MySQL INNER JOIN

Last Updated: 27 July, 2023

In MySQL, the INNER JOIN is used to combine rows from two or more tables based on a related column between them. It selects records that have matching values in both tables.

The syntax for the INNER JOIN is as follows:

 SELECT column_list
 FROM table1
 INNER JOIN table2
    ON table1.column_name = table2.column_name;

For example, let's say we have two tables: customers and orders. The customers table has a column called cust_id, and the orders table has a column called customer_id as well. We can use an INNER JOIN to retrieve the orders placed by customers:

Table: customers

  +---------+--------+--------+------+----------------+
  | cust_id | name   | gender | age  | email          |
  +---------+--------+--------+------+----------------+
  |     101 | Ramesh | Male   |   25 | ramesh@xyz.com |
  |     102 | Umesh  | Male   |   40 | umesh@xyz.com  |
  |     103 | Radha  | Female |   28 | radha@xyz.com  |
  |     104 | John   | Male   |   42 | john@xyz.com   |
  +---------+--------+--------+------+----------------+

Table: orders

  +----------+------------+------------+-------------+
  | order_id | order_item | order_date | customer_id |
  +----------+------------+------------+-------------+
  |      201 | Pen        | 2023-02-18 |         101 |
  |      202 | Book       | 2022-12-25 |         101 |
  |      203 | Book       | 2023-07-12 |         102 |
  |      204 | Pencil     | 2022-09-11 |         102 |
  |      205 | Pen        | 2023-04-08 |         103 |
  |      206 | Note Book  | 2023-06-20 |         104 |
  |      207 | Book       | 2022-11-14 |         104 |
  +----------+------------+------------+-------------+

To retrieve the customer's name and their corresponding orders, we use an INNER JOIN

  SELECT cust.name, ord.order_item, ord.order_date
  FROM customers cust
  INNER JOIN orders ord
  ON cust.cust_id = ord.customer_id;

The result of the query will be:

  +--------+------------+------------+
  | name   | order_item | order_date |
  +--------+------------+------------+
  | Ramesh | Pen        | 2023-02-18 |
  | Ramesh | Book       | 2022-12-25 |
  | Umesh  | Book       | 2023-07-12 |
  | Umesh  | Pencil     | 2022-09-11 |
  | Radha  | Pen        | 2023-04-08 |
  | John   | Note Book  | 2023-06-20 |
  | John   | Book       | 2022-11-14 |
  +--------+------------+------------+

This query combines the "customers" and "orders" tables using the common column "cust_id" from "customers" and "customer_id" from "orders," and selects the customer's name and their corresponding order item and order dates.

Note: The INNER JOIN can be combined with other clauses like WHERE or ORDER BY to further refine the query results.