MySQL Tutorial/Table Join/ANSI 92 Join
CROSS JOIN
A CROSS JOIN returns all the records from all the tables mentioned in the JOIN.
This is also referred to as a Cartesian join.
The syntax for a CROSS JOIN would look like the following:
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> create table job (
-> id int, -> title VARCHAR(20) -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into job (id, title) values (1,"Tester"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (2,"Accountant"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (3,"Developer"); Query OK, 1 row affected (0.02 sec) mysql> insert into job (id, title) values (4,"Coder"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (5,"Director"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (6,"Mediator"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (7,"Proffessor"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (8,"Programmer"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (9,"Developer"); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from job; +------+------------+ | id | title | +------+------------+ | 1 | Tester | | 2 | Accountant | | 3 | Developer | | 4 | Coder | | 5 | Director | | 6 | Mediator | | 7 | Proffessor | | 8 | Programmer | | 9 | Developer | +------+------------+ 9 rows in set (0.02 sec) mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> The old way: mysql> mysql> SELECT C.First_Name, C.Last_Name, O.title
-> FROM Employee as C, job as O;
+------------+-----------+------------+ | First_Name | Last_Name | title | +------------+-----------+------------+ | Jason | Martin | Tester | | Alison | Mathews | Tester | | James | Smith | Tester | | Celia | Rice | Tester | | Robert | Black | Tester | | Linda | Green | Tester | | David | Larry | Tester | | James | Cat | Tester | | Jason | Martin | Accountant | | Alison | Mathews | Accountant | | James | Smith | Accountant | | Celia | Rice | Accountant | | Robert | Black | Accountant | | Linda | Green | Accountant | | David | Larry | Accountant | | James | Cat | Accountant | | Jason | Martin | Developer | | Alison | Mathews | Developer | | James | Smith | Developer | | Celia | Rice | Developer | | Robert | Black | Developer | | Linda | Green | Developer | | David | Larry | Developer | | James | Cat | Developer | | Jason | Martin | Coder | | Alison | Mathews | Coder | | James | Smith | Coder | | Celia | Rice | Coder | | Robert | Black | Coder | | Linda | Green | Coder | | David | Larry | Coder | | James | Cat | Coder | | Jason | Martin | Director | | Alison | Mathews | Director | | James | Smith | Director | | Celia | Rice | Director | | Robert | Black | Director | | Linda | Green | Director | | David | Larry | Director | | James | Cat | Director | | Jason | Martin | Mediator | | Alison | Mathews | Mediator | | James | Smith | Mediator | | Celia | Rice | Mediator | | Robert | Black | Mediator | | Linda | Green | Mediator | | David | Larry | Mediator | | James | Cat | Mediator | | Jason | Martin | Proffessor | | Alison | Mathews | Proffessor | | James | Smith | Proffessor | | Celia | Rice | Proffessor | | Robert | Black | Proffessor | | Linda | Green | Proffessor | | David | Larry | Proffessor | | James | Cat | Proffessor | | Jason | Martin | Programmer | | Alison | Mathews | Programmer | | James | Smith | Programmer | | Celia | Rice | Programmer | | Robert | Black | Programmer | | Linda | Green | Programmer | | David | Larry | Programmer | | James | Cat | Programmer | | Jason | Martin | Developer | | Alison | Mathews | Developer | | James | Smith | Developer | | Celia | Rice | Developer | | Robert | Black | Developer | | Linda | Green | Developer | | David | Larry | Developer | | James | Cat | Developer | +------------+-----------+------------+ 72 rows in set (0.00 sec) mysql> The ANSI-92 way: mysql> mysql> SELECT C.First_Name, C.Last_Name, O.title
-> FROM Employee as C -> CROSS JOIN Job as O;
+------------+-----------+------------+ | First_Name | Last_Name | title | +------------+-----------+------------+ | Jason | Martin | Tester | | Alison | Mathews | Tester | | James | Smith | Tester | | Celia | Rice | Tester | | Robert | Black | Tester | | Linda | Green | Tester | | David | Larry | Tester | | James | Cat | Tester | | Jason | Martin | Accountant | | Alison | Mathews | Accountant | | James | Smith | Accountant | | Celia | Rice | Accountant | | Robert | Black | Accountant | | Linda | Green | Accountant | | David | Larry | Accountant | | James | Cat | Accountant | | Jason | Martin | Developer | | Alison | Mathews | Developer | | James | Smith | Developer | | Celia | Rice | Developer | | Robert | Black | Developer | | Linda | Green | Developer | | David | Larry | Developer | | James | Cat | Developer | | Jason | Martin | Coder | | Alison | Mathews | Coder | | James | Smith | Coder | | Celia | Rice | Coder | | Robert | Black | Coder | | Linda | Green | Coder | | David | Larry | Coder | | James | Cat | Coder | | Jason | Martin | Director | | Alison | Mathews | Director | | James | Smith | Director | | Celia | Rice | Director | | Robert | Black | Director | | Linda | Green | Director | | David | Larry | Director | | James | Cat | Director | | Jason | Martin | Mediator | | Alison | Mathews | Mediator | | James | Smith | Mediator | | Celia | Rice | Mediator | | Robert | Black | Mediator | | Linda | Green | Mediator | | David | Larry | Mediator | | James | Cat | Mediator | | Jason | Martin | Proffessor | | Alison | Mathews | Proffessor | | James | Smith | Proffessor | | Celia | Rice | Proffessor | | Robert | Black | Proffessor | | Linda | Green | Proffessor | | David | Larry | Proffessor | | James | Cat | Proffessor | | Jason | Martin | Programmer | | Alison | Mathews | Programmer | | James | Smith | Programmer | | Celia | Rice | Programmer | | Robert | Black | Programmer | | Linda | Green | Programmer | | David | Larry | Programmer | | James | Cat | Programmer | | Jason | Martin | Developer | | Alison | Mathews | Developer | | James | Smith | Developer | | Celia | Rice | Developer | | Robert | Black | Developer | | Linda | Green | Developer | | David | Larry | Developer | | James | Cat | Developer | +------------+-----------+------------+ 72 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> drop table job; Query OK, 0 rows affected (0.00 sec) mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql> mysql></source>
INNER JOIN
During an INNER JOIN, all records that are unmatched are discarded.
Only the matched rows are displayed.
This is the default type of join, so the word INNER is optional.
This type of join is based on the criteria in the JOIN clause.
<source lang="sql">
mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.06 sec) mysql> mysql> create table job (
-> id int, -> title VARCHAR(20) -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into job (id, title) values (1,"Tester"); Query OK, 1 row affected (0.02 sec) mysql> insert into job (id, title) values (2,"Accountant"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (3,"Developer"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (4,"Coder"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (5,"Director"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (6,"Mediator"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (7,"Proffessor"); Query OK, 1 row affected (0.02 sec) mysql> insert into job (id, title) values (8,"Programmer"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (9,"Developer"); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from job; +------+------------+ | id | title | +------+------------+ | 1 | Tester | | 2 | Accountant | | 3 | Developer | | 4 | Coder | | 5 | Director | | 6 | Mediator | | 7 | Proffessor | | 8 | Programmer | | 9 | Developer | +------+------------+ 9 rows in set (0.00 sec) mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> SELECT C.First_Name, C.Last_Name, O.title
-> FROM employee as C -> INNER JOIN job as O ON C.ID = O.ID;
+------------+-----------+------------+ | First_Name | Last_Name | title | +------------+-----------+------------+ | Jason | Martin | Tester | | Alison | Mathews | Accountant | | James | Smith | Developer | | Celia | Rice | Coder | | Robert | Black | Director | | Linda | Green | Mediator | | David | Larry | Proffessor | | James | Cat | Programmer | +------------+-----------+------------+ 8 rows in set (0.00 sec) mysql> The old way to create an INNER JOIN would look like the following: mysql> mysql> SELECT C.First_Name, C.Last_Name, O.title
-> FROM Employee as C, job as O -> WHERE C.ID = O.ID;
+------------+-----------+------------+ | First_Name | Last_Name | title | +------------+-----------+------------+ | Jason | Martin | Tester | | Alison | Mathews | Accountant | | James | Smith | Developer | | Celia | Rice | Coder | | Robert | Black | Director | | Linda | Green | Mediator | | David | Larry | Proffessor | | James | Cat | Programmer | +------------+-----------+------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> drop table job; Query OK, 0 rows affected (0.00 sec) mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql> mysql></source>
LEFT JOIN
A LEFT JOIN returns all rows from the left table in a join.
All the rows from the table on the left side of the equation will be returned, regardless of whether they have a match with the table on the right side.
If there is no match, a NULL value will be returned.
<source lang="sql">
mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.02 sec) mysql> mysql> create table job (
-> id int, -> title VARCHAR(20) -> );
Query OK, 0 rows affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into job (id, title) values (1,"Tester"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (2,"Accountant"); Query OK, 1 row affected (0.02 sec) mysql> insert into job (id, title) values (3,"Developer"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (4,"Coder"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (5,"Director"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (6,"Mediator"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (7,"Proffessor"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (8,"Programmer"); Query OK, 1 row affected (0.00 sec) mysql> insert into job (id, title) values (9,"Developer"); Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from job; +------+------------+ | id | title | +------+------------+ | 1 | Tester | | 2 | Accountant | | 3 | Developer | | 4 | Coder | | 5 | Director | | 6 | Mediator | | 7 | Proffessor | | 8 | Programmer | | 9 | Developer | +------+------------+ 9 rows in set (0.00 sec) mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> SELECT C.First_Name, C.Last_Name, O.title
-> FROM Employee as C -> LEFT JOIN job as O ON C.ID = O.ID;
+------------+-----------+------------+ | First_Name | Last_Name | title | +------------+-----------+------------+ | Jason | Martin | Tester | | Alison | Mathews | Accountant | | James | Smith | Developer | | Celia | Rice | Coder | | Robert | Black | Director | | Linda | Green | Mediator | | David | Larry | Proffessor | | James | Cat | Programmer | +------------+-----------+------------+ 8 rows in set (0.00 sec) mysql> The old way: mysql> mysql> SELECT C.First_Name, C.Last_Name, O.title
-> FROM Employee AS C, job as O -> WHERE C.ID = O.ID;
+------------+-----------+------------+ | First_Name | Last_Name | title | +------------+-----------+------------+ | Jason | Martin | Tester | | Alison | Mathews | Accountant | | James | Smith | Developer | | Celia | Rice | Coder | | Robert | Black | Director | | Linda | Green | Mediator | | David | Larry | Proffessor | | James | Cat | Programmer | +------------+-----------+------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> mysql> drop table job; Query OK, 0 rows affected (0.00 sec) mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql> mysql></source>
The basic format of the ANSI-92 JOIN
<source lang="sql">
SELECT Column List FROM table name JOIN table name ON join criteria WHERE condition criteria</source>
