JoinTable.sql - Example of Join Tables

This section provides tutorial examples on how different join operations work: CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN.

To validate the join table logics mentioned in the previous section, I wrote the following SQL code, JointTable.sql:

-- JoinTable.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
-- Creating user table
DROP TABLE IF EXISTS User;
CREATE TABLE User (ID INT, Login CHAR(8), Dept_ID INT);
INSERT INTO User VALUES (1,'herong',1);
INSERT INTO User VALUES (2,'mike',2);
INSERT INTO User VALUES (3,'bill',3);
INSERT INTO User VALUES (4,'mary',3);
INSERT INTO User VALUES (5,'lisa',5);
-- Creating dept table
DROP TABLE IF EXISTS Dept;
CREATE TABLE IF NOT EXISTS Dept (ID INT, Name CHAR(16));
INSERT INTO Dept VALUES (1,'Math');
INSERT INTO Dept VALUES (3,'Chem');
INSERT INTO Dept VALUES (4,'Law');
INSERT INTO Dept VALUES (5,'English');
INSERT INTO Dept VALUES (5,'Latin');
-- Generating join tables
SELECT 'Running cross join' AS '---';
SELECT * FROM User CROSS JOIN Dept;
SELECT 'Running inner join' AS '---';
SELECT * FROM User INNER JOIN Dept ON User.Dept_ID=Dept.ID;
SELECT 'Running left outer join' AS '---';
SELECT * FROM User LEFT OUTER JOIN Dept ON User.Dept_ID=Dept.ID;
SELECT 'Running right outer join' AS '---';
SELECT * FROM User RIGHT OUTER JOIN Dept ON User.Dept_ID=Dept.ID;

Note that:

Output of JoinTabl.sql:

---
Running cross join
ID      Login   Dept_ID ID      Name
1       herong  1       1       Math
2       mike    2       1       Math
3       bill    3       1       Math
4       mary    3       1       Math
5       lisa    5       1       Math
1       herong  1       3       Chem
2       mike    2       3       Chem
3       bill    3       3       Chem
4       mary    3       3       Chem
5       lisa    5       3       Chem
1       herong  1       4       Law
2       mike    2       4       Law
3       bill    3       4       Law
4       mary    3       4       Law
5       lisa    5       4       Law
1       herong  1       5       English
2       mike    2       5       English
3       bill    3       5       English
4       mary    3       5       English
5       lisa    5       5       English
1       herong  1       5       Latin
2       mike    2       5       Latin
3       bill    3       5       Latin
4       mary    3       5       Latin
5       lisa    5       5       Latin
---
Running inner join
ID      Login   Dept_ID ID      Name
1       herong  1       1       Math
3       bill    3       3       Chem
4       mary    3       3       Chem
5       lisa    5       5       English
5       lisa    5       5       Latin
---
Running left outer join
ID      Login   Dept_ID ID      Name
1       herong  1       1       Math
2       mike    2       NULL    NULL
3       bill    3       3       Chem
4       mary    3       3       Chem
5       lisa    5       5       English
5       lisa    5       5       Latin
---
Running right outer join
ID      Login   Dept_ID ID      Name
1       herong  1       1       Math
3       bill    3       3       Chem
4       mary    3       3       Chem
NULL    NULL    NULL    4       Law
5       lisa    5       5       English
5       lisa    5       5       Latin

Notes on the output:

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

 Perl 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 - 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

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

 Storage Engines in MySQL Server

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB