MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
JOIN - Operation to Join Two Tables
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
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl 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 - Operation to Join Two Tables
JoinTable.sql - Example of Join Tables
WHERE Clause of a SELECT Statement
ORDER BY Clause of a SELECT Statement
GROUP BY Clause of a SELECT Statement
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux