A2-02-12.DML-MySQL Join Made Easy For Beginners

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

MySQL Join Made Easy For Beginners

 

Summary: in this tutorial, you will learn various MySQL join statements to query data from multiple tables.

Introduction to MySQL join statements

A relational database consists of multiple related tables linking together using common columns which are known as foreign key columns. Because of this, data in each table is incomplete from the business perspective.

For example, in the sample database, we have the orders and  orderdetails tables that are linked using the orderNumber column.

To get complete orders’ data, you need to query data from both orders and  orderdetails table.

And that’s why MySQL JOIN comes into the play.

A MySQL join is a method of linking data from one (self-join) or more tables based on values of the common column between tables.

MySQL supports the following types of joins:

  1. Cross join
  2. Inner join
  3. Left join
  4. Right join

To join tables, you use the CROSS JOININNER JOINLEFT JOIN or RIGHT JOIN clause for the corresponding type of join. The join clause is used in the SELECT statement appeared after the FROMclause.

Notice that MySQL does not support full outer join.

To make easy for you to understand each type of join, we will use the t1 and t2 tables with the following structures:

Both t1 and t2 tables have the pattern column, which is also the common column between tables.

The following statements insert data into both t1 and t2 tables:

And the pictures below illustrate data from both t1 and t2 tables:

MySQL Join - t1 table data

MySQL CROSS JOIN

The CROSS JOIN makes a Cartesian product of rows from multiple tables. Suppose, you join t1 and t2tables using the CROSS JOIN, the result set will include the combinations of rows from the t1 table with the rows in the t2 table.

To perform cross join, you use the CROSS JOIN clause as in the following statement:

The following shows the result set of the query:

MySQL Join - cross join result

As you can see, each row in the t1 table combines with rows in the t2 table to form the Cartesian product.

The following picture illustrates the CROSS JOIN between t1 and t2 tables.

MySQL Join - cross join illustration

MySQL INNER JOIN

To form anINNER JOIN, you need a condition which is known as a join-predicate. An INNER JOIN requires rows in the two joined tables to have matching column values. The INNER JOIN creates the result set by combining column values of two joined tables based on the join-predicate.

To join two tables, the INNER JOIN compares each row in the first table with each row in the second table to find pairs of rows that satisfy the join-predicate. Whenever the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of the two tables are included in the result set.

The following statement uses the INNER JOIN clause to join t1 and t2 tables:

In this statement, the following expression is the join-predicate:

It means that rows in t1 and t2 tables must have the same values in the pattern column to be included in the result.

The following illustrates the result of the query:

MySQL Join - inner join result

The following picture illustrates the INNER JOIN between t1 and t2 tables:

MySQL Join - inner join illustration

In this illustration, the rows in both tables must have the same pattern to be included in the result set.

MySQL LEFT JOIN

Similar to an INNER JOIN, a LEFT JOIN also requires a join-predicate. When joining two tables using a LEFT JOIN, the concepts of left table and right table are introduced.

Unlike an INNER JOIN, a LEFT JOIN returns all rows in the left table including rows that satisfy join-predicate and rows do not. For the rows that do not match the join-predicate, NULLs appear in the columns of the right table in the result set.

The following statement uses the LEFT JOIN clause to join t1 and t2 tables:

MySQL Join - left join result

As you can see, all rows in the t1 table are included in the result set. For the rows in the t1 table (left table) that do not have any matching row in the t2 table (right table), NULLs are used for columns in t2 table.

The following picture illustrates the LEFT JOIN between t1 and t2 tables:

MySQL Join - left join illustration

In this illustration, the following rows share the same pattern: (2 and A), (3 and B). The row with id 1 in the t1 table has no matching row in the t2 table, therefore, NULL are used for columns of the  t2 table in the result set.

MySQL RIGHT JOIN

RIGHT JOIN is similar to the LEFT JOIN except that the treatment of tables is reversed. With a RIGHT JOIN, every row from the right table ( t2) will appear in the result set. For the rows in the right table that do not have the matching rows in the left table ( t1), NULLs appear for columns in the left table ( t1).

The following statement joins t1 and t2 tables using RIGHT JOIN:

In this result, all rows from the right table ( t2) appear in the result set. For the rows in the right table ( t2) that have no matching rows in the left table ( t1), NULL appears for columns of the left table ( t1).

The following picture illustrates the RIGHT JOIN between t1 and t2 tables:

MySQL Join - right join illustration

In this tutorial, you have learned various MySQL join statements including cross join, inner join, left join and right join to query data from two or more tables.

posted @ 2018-08-21 16:56  zhuntidaoren  阅读(153)  评论(0编辑  收藏  举报