MySQL Join Made Easy For Beginners

Summary: In this tutorial, you will learn several MySQL merge clauses in the SELECT statement to query data from two tables.

Introduction to MySQL join clauses

A relational database consists of several related tables that are joined together by common columns, which are known as foreign key columns. Because of this, the data in each table is incomplete from a business perspective.

For example, in the sample database, we have the orders and

orderdetails tables that are linked using the column orderNumber

:

For complete order information, you should refer to the data in the order tables and order details

.

That’s why unions come into play.

A merge is a method of linking data between one (automerge) or more tables based on values in the common column between tables.

MySQL supports the following types of joins:

1. Inner join Left

join

2. Right

join Cross join

3. To

join tables, use the cross join, inner join, left join, or right join

clause. The join clause is used in the SELECT statement that appears after the FROM clause.

Note that MySQL has not yet supported FULL EXTERNAL JOIN.

Configuring example

tables

First, create two tables named members and committees:

CREATE TABLE members ( member_id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (member_id) ); CREATE TABLE committees ( committee_id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (committee_id) ); Code language: SQL (Structured Query Language) (sql)

Second, insert some rows in the members and committees tables :

INSERT INTO members(name) VALUES(‘John’),(‘Jane’),(‘Mary’),(‘David’),(‘Amelia’); INSERT INTO committees(name) VALUES(‘John’),(‘Mary’),(‘Amelia’),(‘Joe’); Code language: SQL (Structured Query Language) (sql)

Third, query data from the members and committees tables:

SELECT * FROM members; Code language: SQL (Structured Query Language) (sql)+-+-+ | member_id | Name | +-+-+ | 3 > John | | 0 > Jane | | 1 > Mary | | 4 | David | | 5 | Amelia | +-+-+ 5 rows together (0.00 sec)Code language: plain text (plain text)SELECT * FROM committees; Code language: SQL (Structured Query Language) (sql)+-+-+ | committee_id | Name | +-+-+ | 3 > John | | 0 > Mary | | 1 > Amelia | | 4 | Joe | +-+-+ 4 rows together (0.00 sec)Code language: plain text (plain text)

Some members are committee members and some are not. On the other hand, some committee members are at the members’ table, others are not.

MySQL INNER JOIN Clause

The

following is the basic syntax of the internal join clause that joins two tables table_1 and table_2:

SELECT column_list OF table_1 table_2 IN join_condition; Code language: SQL (Structured Query Language) (sql)The home join clause

joins two tables based on a condition known as the join predicate

.

The merge clause compares each row in the first table to each row in the second table.

If the values in both rows

satisfy the union condition, the inner merge clause creates a new row whose column contains all the columns in the two rows of both tables and includes this new row in the result set. In other words, the internal join clause includes only the matching rows of both tables.

If the join condition uses the equality operator (=) and the column names of both tables used for matching are the same, you can use the USING clause instead:

SELECT column_list OF table_1 INTERNAL JUNCTION table_2 USING (column_name); Code language: SQL (Structured Query Language) (sql)

The following statement uses an internal join clause to find members who are also committee members:

SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee FROM members m INNER JOIN committees c ON c.name = m.name; Code language: SQL (Structured Query Language) (sql)+-+-+-+-+ | member_id | Member | committee_id | Committee | +-+-+-+-+ | 3 > John | 3 > John | | 1 > Maria | 0 > Mary | | 5 | Amelia | 1 > Amelia | +-+-+-+-+ 3 rows together (0.00 sec)Code language: plain text (plain text)

In this example, the inner merge clause uses the values of the name columns in both table members and committees to match.

The following Venn diagram illustrates the internal union

:

Because both tables use the same column to match, you can use the USING clause as shown in the following query:

SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee FROM members m INNER JOIN committees c USING(name); Code language: SQL (Structured Query Language) (sql)

MySQL LEFT JOIN clause Similar

to an inner join

, a left join also requires a join predicate. When you join two tables using a left join, the concepts of left and right tables are introduced.

The left combination selects the data from the left table. For each row in the left table, the left join is compared to all rows in the right table.

If the values in the two rows

satisfy the union condition, the left merge clause creates a new row whose columns contain all the columns in the rows in both tables and includes this row in the result set.

If the values in the two rows do not match, the left merge clause continues to create a new row whose columns contain columns from the left table row and NULL for the columns in the right table row.

In other words, the left join selects all the data in the left table, whether matching rows exist in the right table or not.

If no matching rows are found in the right table, the left join uses NULL for the columns in the row in the right table in the result set.

This is the basic syntax of a merge clause on the left that joins two tables:

SELECT column_list ON table_1 LEFT JOIN table_2 IN join_condition; Code language: SQL (Structured Query Language) (sql)

The left combination also supports the USING clause if the column used to match both tables is the same:

SELECT column_list FROM table_1 LEFT JOIN table_2 USING (column_name); Code language: SQL (Structured Query Language) (sql)

The following example uses a left join clause to join members to the committee table:

SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee FROM members m LEFT JOIN committees c USING(name); Code language: SQL (Structured Query Language) (sql)+-+-+-+-+ | member_id | Member | committee_id | Committee | +-+-+-+-+ | 3 > John | 3 > John | | 0 > Jane | NULL | NULL | | 1 > Maria | 0 > Mary | | 4 | David | NULL | NULL | | 5 | Amelia | 1 > Amelia | +-+-+-+-+ 5 rows together (0.00 sec)Code language: plain text (plain text)

The following Venn diagram illustrates

the left join:

this statement uses the left join clause with the syntax using

:SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee OF members m LEFT JOIN committees c USING(name); Code language: SQL (Structured Query Language) (sql)To find non-committee

members, add a WHERE clause and an IS NULL operator as follows:

SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee FROM members m LEFT JOIN committees c USING(name) WHERE c.committee_id IS NULL; Code language: SQL (Structured Query Language) (sql)+-+-+-+-+ | member_id | Member | committee_id | Committee | +-+-+-+-+ | 0 > Jane | NULL | NULL | | 4 | David | NULL | NULL | +-+-+-+-+ 2 rows together (0.00 sec)Code language: plain text (plain text)

Generally, this query pattern can find

rows in the left table that do not have corresponding rows in the right table.

This Venn diagram illustrates how to use the left combination to select rows that exist only in the left table:

MySQL RIGHT JOIN clause The

right join

clause is similar to the left join clause, except that the treatment of the left and right tables is reversed. The right merge starts selecting data from the right table instead of the left table.

The right merge clause selects all rows in the right table and matches the rows in the left table. If a row in the right table has no matching rows in the left table, the column in the left table will have NULL in the final result set.

This is the syntax of the correct union:

SELECT column_list OF table_1 RIGHT JOIN table_2 IN join_condition; Code language: SQL (Structured Query Language) (sql)

Similar to the left merge clause, the right clause also supports the

USING :SELECT syntax column_list OF table_1 RIGHT UNION table_2 USING (column_name); Code language: SQL (Structured Query Language) (sql)To find rows in the

right table that do not have corresponding rows in the left table, also use a WHERE clause with the operator

IS NULL:SELECT column_list FROM table_1 RIGHT JOIN table_2 USING (column_name) WHERE column_table_1 IS NULL; Code language: SQL (Structured Query Language) (sql)

This statement uses the correct join to join the member and committee tables:

SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee FROM members m RIGHT JOIN committees c in c.name = m.name; Code language: SQL (Structured Query Language) (sql)+-+-+-+-+ | member_id | Member | committee_id | Committee | +-+-+-+-+ | 3 > John | 3 > John | | 1 > Maria | 0 > Mary | | 5 | Amelia | 1 > Amelia | | NULL | NULL | 4 | Joe | +-+-+-+-+ 4 rows together (0.00 sec)Code language: plain text (plain text)

This Venn diagram illustrates the correct union:

The following statement uses the right join clause with the USING syntax

:SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee FROM members m RIGHT JOIN committees c USING(name); Code language: SQL (Structured Query Language) (sql)

To find committee members who are not in the members table, use this query:

SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee FROM members m RIGHT JOIN committees c USING(name) WHERE m.member_id IS NULL; Code language: SQL (Structured Query Language) (sql)+-+-+-+-+ | member_id | Member | committee_id | Committee | +-+-+-+-+ | NULL | NULL | 4 | Joe | +-+-+-+-+ 1 row in aggregate (0.00 sec)Code language: plain text (plain text)

This Venn diagram illustrates how to use the right join to select data that exists only in the right table:

MySQL CROSS JOIN Clause

Unlike Join

internal, the left union and the right union, the cross union clause does not have a union condition.

Cross-joining makes a Cartesian product of rows of joined tables. Cross-merging merges each row in the first table with each row in the right table to set the result set.

Suppose the first table has n rows and the second table has m rows. The cross-join joining the tables will return nxm rows.

The following is the syntax of the cross-join clause:

SELECT table_1 table_2 CROSS JOINT select_list; Code language: SQL (Structured Query Language) (sql)

This example uses the cross join clause to join members with committees tables

:SELECT m.member_id, m.name AS member, c.committee_id, c.name AS committee FROM members m CROSS JOIN committees c;Code language: SQL (Structured Query Language) (sql)+-+-+-+-+ | member_id | Member | committee_id | Committee | +-+-+-+-+ | 3 > John | 4 | Joe | | 3 > John | 1 > Amelia | | 3 > John | 0 > Mary | | 3 > John | 3 > John | | 0 > Jane | 4 | Joe | | 0 > Jane | 1 > Amelia | | 0 > Jane | 0 > Mary | | 0 > Jane | 3 > John | | 1 > Maria | 4 | Joe | | 1 > Maria | 1 > Amelia | | 1 > Maria | 0 > Mary | | 1 > Maria | 3 > John | | 4 | David | 4 | Joe | | 4 | David | 1 > Amelia | | 4 | David | 0 > Mary | | 4 | David | 3 > John | | 5 | Amelia | 4 | Joe | | 5 | Amelia | 1 > Amelia | | 5 | Amelia | 0 > Mary | | 5 | Amelia | 3 > John | +-+-+-+-+ 20 rows together (0.00 sec)Code language: plain text (plain text)

Cross-joining is useful for generating planning data. For example, you can conduct sales planning by cross-combining customers, products, and years.

In this tutorial, you learned several MySQL merge statements, including cross-join, inner join, left join, and right join, to query data from two tables.