JoinTable.sql - Example of Join Tables

MySQL Tutorials - Herong's Tutorial Examples

∟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:

"*" can be used as selection expression. It will be evaluated to a list of values from all columns in the select table.
A select expression will have system default column name in the output table. You can change the default column name by using "AS new_name" option.
When referring to a column in a table, you can use the fully quantified column name: table_name.column_name.

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:

Surprisingly, the cross join was performed with the outer loop on the right table columns. This is different than my expectation.
Inner join, left outer join, and right outer join were performed as expected.