A2-02-13.DML- MySQL INNER JOIN

时间:2022-09-15 23:03:50

转载自:http://www.mysqltutorial.org/mysql-inner-join.aspx

MySQL INNER JOIN

 

 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 INNER JOIN clause is an optional part of the SELECT statement. It appears immediately after the FROM clause.

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

  • First, the main table that appears in the FROM clause.
  • Second, the table that you want to join with the main table, which appears in the INNER JOINclause. In theory, you can join a table with many other tables. However, for a better performance, you should limit the number of tables to join.
  • Third, the join condition or join predicate. The join condition appears after the ON keyword of the INNER JOIN clause. The join condition is the rule for matching rows in the main table with the rows in the other tables.

The syntax of the INNER JOIN clause is as follows:

Let’s simplify the syntax above by assuming that we are joining two tables t1 and t2 using the INNER JOIN clause.

For each row in the t1 table, the INNER JOIN clause compares it with each row of the t2 table to check if both of them satisfy the join condition. When the join condition is met, the INNER JOIN will return a new row which consists of columns in both t1 and t2 tables.

Notice that the rows in 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. This logic is also applied when you join more than 2 tables.

The following Venn diagram illustrates how the INNER JOIN clause works. The rows in the result set must appear in both tables: t1 and t2 as shown in the intersection part of two circles.

A2-02-13.DML- MySQL INNER JOINMySQL 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 and ON clauses to avoid the ambiguous column error.

For example, if both t1 and t2 tables have the same column named c , you have to refer to the ccolumn using the table qualifiers as t1.c or t2.c  in the SELECT and ON clauses.

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

MySQL INNER JOIN examples

Let’s look at the products and productlines tables in the sample database.

A2-02-13.DML- MySQL INNER JOIN

In this diagram, the products table has the productLine column referenced to the  productline column of the  productlines table. The productLine column in the products table is called a foreign key column.

Typically, you join tables that have foreign key relationships like the  productlines and products tables.

Now, if you want to get

  • The productCode and productName from the products table.
  • The textDescription of product lines from the productlines table.

To do this, you need to select data from both tables by matching rows based on the productlinecolumns using the INNER JOIN clause as follows:

Try It Out

A2-02-13.DML- MySQL INNER JOIN

Because the joined columns of both tables have the same name  productline, you can use the following syntax:

It returns the same result set however with this syntax you don’t have to use the table aliases.

MySQL INNER JOIN with GROUP BY clause

See the following orders and orderdetails tables.

A2-02-13.DML- MySQL INNER JOIN

You can get the order number, order status and total sales from the orders and orderdetails tables using the INNER JOIN clause with the GROUP BYclause as follows:

Try It Out

A2-02-13.DML- MySQL INNER JOIN

Similarly, the following query is equivalent to the one above:

 

MySQL INNER JOIN using operator other than equal

So far, you have seen that the join predicate used the equal operator (=) for matching rows. In addition, you can use other operators such as greater than ( >), less than ( <), and not-equal ( <>) operator to form the join predicates.

The following query uses a less-than ( <) join to find sales prices of the product whose code is S10_1678that are less than the manufacturer’s suggested retail price (MSRP) for that product.

A2-02-13.DML- MySQL INNER JOIN

In this tutorial, you have learned how to use the MySQL INNER JOIN to query data from multiple tables.