MySQL INNER JOIN

Summary : In this tutorial, you'll learn how to use the MySQL INNER JOIN clause to select data from multiple table s based on join conditions.

Introducing MySQL INNER JOIN clause

The MySQL INNER JOIN clause matches rows in one table with rows in other tables and allows your to query rows that contain columns from both tables.

The MySQL INNER JOIN clause A optional part of the SELECT statement. It appears immediately after the FROM  clause.

Before using MySQL INNER JOIN clause, you have to specify the following criteria:

• First, you had to specify the main table, appears in the FROM  clause.
• Second, need to specify the table, the want to join with the main table, and which appears in the INNER JOIN clause. Theoretically, you can join a table with many tables. However, for better query performance, your should limit the number of tables to join.
• Third, you need to specify the join condition or join predicate. The join condition appears after the keyword of the ON  INNER JOIN  clause. The join condition is the rule for matching rows between the main table and the other tables.

The syntax of the MySQL INNER JOIN clause is as follows:

SELECT Column_listfrom T1inner join T2 on Join_condition1inner join T3 on Join_condition2 ... WHERE where_conditions;

　　

Let's simplify the syntax above by assuming then we are joining both tables and T1 T2 using the INNER JOIN clause.

For each record T1 in the table, the MySQL INNER JOIN clause compares it with each record of the T2 table to check if Both of them satisfy the join condition. When the join condition is matched, it'll return that record, which combine columns in either or both and T1  T2  tables .

Notice the records on both and tables has to be T1 T2 matched based on the join condition. If no match found, the query would return an empty result set.

The logic is applied if we join more than 2 tables.

The following Venn diagram illustrates how the MySQL INNER JOIN clause works.

MySQL INNER JOIN Venn Diagram

Avoid ambiguous column error in MySQL INNER JOIN

If you join multiple tables that has the same column name, you had to use table qualifier to refer to that column in the SELECT clause to avoid ambiguous column error. For example, if both and tables has the T1  T2 same column named C ; SELECT  Er to C column using the table qualifiers as T1.C or T2.C .

To save time typing the table qualifiers, you can use table aliases in the query. For example, you can give the verylongtablename  table a alias and refer to its T columns using T.column instead of verylongtablename.column .

Examples of using MySQL INNER JOIN clause

Let's take a look at both tables: and products  tables in the productlines  sample database.

Now, if you want to get

• The Product Code and product name from the products  table.
• The text description of product lines from the productlines  table.

You need to select data from both tables and match rows by comparing the productline  column from the products  table with the productline  col Umn from the productlines  table as the following query:

SELECT ProductCode,       productName,       textdescriptionfrom products T1inner joins Productlines T2 on t1.productline = T2.productline;

　　

MySQL INNER JOIN with GROUP by clause

We can get the order number, order status and total sales from the orders  and orderdetails tables using the INNER JOIN clause with the GR OUP by clause as follows:

SELECT T1.ordernumber,       status,       SUM (quantityordered * priceeach) totalfrom orders as T1inner JOIN OrderDetails as T2 on t1.ordernumber = T2.ordernumbergroup by ordernumber

　　

In this tutorial, you has learned how to use the MySQL INNER JOIN to query data from multiple tables. You have also learned the use Table qualifier to avoid ambiguous column error in MySQL INNER JOIN clause.

MySQL INNER JOIN