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

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 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

 SELECT Statements

 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

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version