**MySQL Tutorials - Herong's Tutorial Examples** - Version 4.20, by Dr. Herong Yang

JOIN -Operations to Join Two Tables

This section describes JOIN operations to join two tables in different ways: CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN.

A join table is the output table of a join operation on two tables. There are several types of join operations:

1. Cross Join - Takes each row in the left table, and joins onto all rows in the right table. The number of columns of the output table is the number of columns of the left table plus the number of columns of the right table. The number of rows of the output table is the number of rows of the left table times the number of rows of the right table. A cross join operation is also called Cartesian product operation. There are two ways to write a cross join table:

table_l, table_r table_l CROSS JOIN table_r

The cross join operation logic can be expressed as:

Loop on each row in the left table (L) Loop on each row in the right table (R) Generate an output row with all columns of the current row of L and all columns of the current row of R End of loop End of loop

2. Inner Join - Takes the output table of the cross join operation, and filter out rows that do not satisfy the specified join condition, which should be an equality comparison of one column in the left table and one column in the right table. The syntax form of an inner join table is:

table_l INNER JOIN table_r ON table_l.column_l=table_r.column_r

The inner join operation logic can be expressed as:

Loop on each row in the left table (L) Loop on each row in the right table (R) If the value of column_l equals to the value of column_r Generate an output row with all columns of the current row of L and all columns of the current row of R Break the loop on R End if End of loop End of loop

3. Left Outer Join - Takes the output table of the inner join operation, and adds one row for each row in the left table that has no matching rows in the right table. This new output row will contain the row from the left table and null values to occupy the output columns corresponding to the right table. The syntax form of a left outer join table is:

table_l LEFT OUTER JOIN table_r ON table_l.column_l=table_r.column_r

The left outer join operation logic can be expressed as:

Loop on each row in the left table (L) Set match_found = FALSE Loop on each row in the right table (R) If the value of column_l equals to the value of column_r Generate an output row with all columns of the current row of L and all columns of the current row of R Set match_found = TRUE End if End of loop If match_found is FALSE Generate an output row with all columns of the current row of L and null values for columns corresponding to columns of R End if End of loop

4. Right Outer Join - Takes the output table of the inner join operation, and adds one row for each row in the right table that has no matching rows in the left table. This new output row will contain the row from the right table and null values to occupy the output columns corresponding to the left table. The syntax form of a right outer join table is:

table_l RIGHT OUTER JOIN table_r ON table_l.column_l=table_r.column_r

The right outer join operation logic can be expressed as:

Loop on each row in the right table (R) Set match_found = FALSE Loop on each row in the left table (L) If the value of column_l equals to the value of column_r Generate an output row with all columns of the current row of L and all columns of the current row of R Set match_found = TRUE End if End of loop If match_found is FALSE Generate an output row with all columns of the current row of R and null values for columns corresponding to columns of L End if End of loop

*Last update: 2015.*

Table of Contents

MySQL Introduction and Installation

Introduction of MySQL Programs

Perl Programs and MySQL Servers

PHP Programs and MySQL Servers

Java Programs and MySQL Servers

Character Strings and Bit Strings

Table Column Types for Different Types of Values

Using DDL to Create Tables and Indexes

Using DML to Insert, Update and Delete Records

►Using SELECT to Query Database

FROM Clause of a SELECT Statement

►JOIN -Operations to Join Two Tables

JoinTable.sql - Example of Join Tables

WHERE Clause of a SELECT Statement

GROUP BY Clause of a SELECT Statement

ORDER BY Clause of a SELECT Statement

Transaction Management and Isolation Levels

Defining and Calling Stored Procedures