MySQL INNER JOIN with Examples

Inner JOIN is used to return rows from two tables that meet a given condition.

In MySQL, INNER JOIN selects all rows from two participating tables to appear in the result only if both tables meet the conditions specified in the clause ON. JOIN, CROSS JOIN and INNER JOIN have equivalent syntax. In standard SQL, they are not equivalent. INNER JOIN is used with the clause ON, but CROSS JOIN does not use the clause ON.
 

 

 

Syntax:

SELECT column1, column2, ..., column_n
FROM table_A
INNER JOIN table_B
ON table_A.columnX = table_B.columnX;

 

Example :

Let’s take an example of using the clause INNER JOIN to see how it works. For this, we will use the “Customers” and “Orders” tables.
 
Customers table

> SELECT * FROM Customers;

+------------+-----------+--------+-------------------------------+
| CustomerID |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     1      |    Alex   |   25   | 819 Saint Francis Way         |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3      |   Jean    |   35   | 188 Clay Street Indiana       |
|     4      |    Bob    |   40   | 285 Java Lane Missouri        |
+------------+-----------+--------+-------------------------------+

 
Orders table

> SELECT * FROM Orders;

+---------+-----------+----------+-------------+
| OrderID |  NumOrder |   Total  |  CustomerID |
+---------+-----------+----------+-------------+
|     1   |   9901    |  254.00  |      1      |
|     2   |   9902    |  300.96  |      1      |
|     3   |   9903    |  145.03  |      1      |
|     4   |   9904    |  744.33  |      4      |
+---------+-----------+----------+-------------+

The following query will selects all the orders made by the customers:

SELECT Commandes.CommandeID, Clients.Nom
FROM Commandes
INNER JOIN Clients ON Commandes.ClientID = Clients.ClientID;

Output:

+---------+-----------+
| OrderID |   Name    |
+---------+-----------+
|     1   |   Alex    |
|     2   |   Alex    |
|     3   |   Alex    |
|     4   |   Bob     |
+---------+-----------+

The INNER JOIN clause selects all rows in both tables as long as there is a match between the columns. If there are rows in the “Orders” table that do not have a match in the “Customers” table, those orders will not be displayed!
 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More