Summary: in this tutorial, you will learn how to use MySQL INNER JOIN clause to select data from multiple tables 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 you to query rows that contain columns from both tables.
The MySQL INNER JOIN clause an optional part of the SELECT statement. It appears immediately after the FROMclause.
Before using MySQL INNER JOIN clause, you have to specify the following criteria:
First, you have to specify the main table that appears in the
FROMclause.Second, you need to specify the table that you want to join with the main table, which appears in the
INNER JOINclause. Theoretically, you can join a table with many tables. However, for better query performance, you 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
ONof theINNER JOINclause. 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_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2 ... WHERE where_conditions;
Let’s simplify the syntax above by assuming that we are joining two tables T1 and T2 using the INNER JOIN clause.
For each record in the T1 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 will return that record that combine columns in either or both T1 and T2 tables.
Notice that the records on both T1 and T2 tables have to be matched based on the join condition. If no match found, the query will 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 have the same column name, you have to use table qualifier to refer to that column in the SELECT clause to avoid ambiguous column error. For example, if both T1 and T2 tables have the same column named C; in the SELECT clause, you have to refer 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 an alias T and refer to its columns using T.column instead of verylongtablename.column.
Examples of using MySQL INNER JOIN clause
Let’s take a look at two tables: products and productlines tables in the sample database.
Now, if you want to get
The product code and product name from the
productstable.The text description of product lines from the
productlinestable.
You need to select data from both tables and match rows by comparing the productline column from the productstable with the productline column from the productlines table as the following query:
SELECT productCode, productName, textDescription FROM products T1 INNER JOIN 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 GROUP BY clause as follows:
SELECT T1.orderNumber, status, SUM(quantityOrdered * priceEach) total FROM orders AS T1 INNER JOIN orderdetails AS T2 ON T1.orderNumber = T2.orderNumber GROUP BY orderNumber
In this tutorial, you have learned how to use MySQL INNER JOIN to query data from multiple tables. You have also learned how to use table qualifier to avoid ambiguous column error in MySQL INNER JOIN clause.
Related Tutorials
共同学习，写下你的评论
评论加载中...
作者其他优质文章
