I never remember this JOIN database stuff unless I have a reminder like this.

JOINs_in_MySQL.sql

-- These examples are from http://en.wikipedia.org/wiki/Join_(SQL)

DROP DATABASE IF EXISTS dumb_example;
CREATE DATABASE dumb_example;
USE dumb_example;

CREATE TABLE department
(
  DepartmentID INT,
  DepartmentName VARCHAR(20)
);
 
CREATE TABLE employee
(
  LastName VARCHAR(20),
  EmployeeID INT,
  DepartmentID INT,
  Country VARCHAR(20)
);

CREATE TABLE laptop
(
  EmployeeID INT,
  Type VARCHAR(20)
);

-- There are no employees in the Marketing department. 
INSERT INTO department (DepartmentID, DepartmentName) VALUES(31, 'Sales');
INSERT INTO department (DepartmentID, DepartmentName) VALUES(33, 'Engineering');
INSERT INTO department (DepartmentID, DepartmentName) VALUES(34, 'Clerical');
INSERT INTO department (DepartmentID, DepartmentName) VALUES(35, 'Marketing');

-- Employee John is not assigned to any department.
INSERT INTO employee (LastName, DepartmentID, EmployeeID, Country) VALUES('Rafferty',   31,   120, 'Australia');
INSERT INTO employee (LastName, DepartmentID, EmployeeID, Country) VALUES('Jones',      33,   122, 'Australia');
INSERT INTO employee (LastName, DepartmentID, EmployeeID, Country) VALUES('Heisenberg', 33,   135, 'Australia');
INSERT INTO employee (LastName, DepartmentID, EmployeeID, Country) VALUES('Robinson',   34,   83,  'United States');
INSERT INTO employee (LastName, DepartmentID, EmployeeID, Country) VALUES('Smith',      34,   209, 'Germany');
INSERT INTO employee (LastName, DepartmentID, EmployeeID, Country) VALUES('John',       NULL, 57,  'Germany');

INSERT INTO laptop (EmployeeID, Type) VALUES(57,  'Chromebook');
INSERT INTO laptop (EmployeeID, Type) VALUES(83,  'HP');
INSERT INTO laptop (EmployeeID, Type) VALUES(120, 'Lenovo');
INSERT INTO laptop (EmployeeID, Type) VALUES(122, 'MacBook Pro');
INSERT INTO laptop (EmployeeID, Type) VALUES(135, 'MacBook Air');
INSERT INTO laptop (EmployeeID, Type) VALUES(209, 'Lenovo');

-- ---------------------------------------------------------------------------
--                                                                          --
--   Cross join - no where clause, just a cartesian product of two tables   --
--   MySQL does not assume DepartmentID is the same in the two tables.      --
--                                                                          --
-- ---------------------------------------------------------------------------

SELECT 'Cross join is a cartesian product of the rows of the two tables';
SELECT * FROM employee, department;
SELECT * FROM employee CROSS JOIN department;

-- ---------------------------------------------------------------------------
--                                                                          --
--   Inner join - a cross join filtered by a join predicate (WHERE, ON)     --
--   Employee John is excluded because his DepartmentID is NULL and         --
--   therefore won't be matched with any rows in the department table.      --
--                                                                          --
-- ---------------------------------------------------------------------------

SELECT 'Inner join is a cross join filtered by a WHERE or ON clause';
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
  FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
  FROM employee, department WHERE employee.DepartmentID = department.DepartmentID;

SELECT 'The USING syntax works only if WHERE class is an equals, not < or >';
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
  FROM employee INNER JOIN department USING (DepartmentID);

SELECT 'NATURAL JOIN creates WHERE (equals) clauses for any same-spelled columns';
SELECT * FROM employee NATURAL JOIN department;

-- ---------------------------------------------------------------------------
--                                                                          --
--   Outer joins differ from inner joins in that rows are included even     --
--   if a NULL prevents them being matched with rows in the other table.    --
--                                                                          --
--   In a left join, every row from the left table appears in the results   --
--   even if it doesn't match a row in the right table. Unmatched rows in   --
--   the right table do not appear in the results.                          --
--                                                                          --
--   In a right join, every row from the right table appears in the         --
--   results even if it doesn't match a row in the left table. Unmatched    --
--   rows in the left table do not appear in the results.                   --
--                                                                          --
--   In a full outer join, every row from both tables appear in the         --
--   results even if they don't match a row in the other table. Every       --
--   row appears somewhere in the results.                                  --
--                                                                          --
-- ---------------------------------------------------------------------------

SELECT 'Left join - results include "John" in the employee table';
SELECT * FROM employee LEFT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

SELECT 'Right join - results include "Marketing" in the department table';
SELECT * FROM employee RIGHT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

SELECT 'Full outer join - results include both "John" and "Marketing"';
SELECT * FROM employee LEFT JOIN department
  ON employee.DepartmentID = department.DepartmentID
UNION      -- Full joins are NOT IMPLEMENTED in MySQL but can be emulated with UNION
SELECT * FROM employee RIGHT JOIN department
  ON employee.DepartmentID = department.DepartmentID;

-- ---------------------------------------------------------------------------
--                                                                          --
--   A self-join is a join of a table with itself. Two variables are        --
--   used to represent the left and right roles of the single table.        --
--                                                                          --
-- ---------------------------------------------------------------------------

SELECT 'Self-join of employee table: find pairs of employees in the same country';
SELECT foo.EmployeeID, foo.LastName, bar.EmployeeID, bar.LastName, foo.Country
  FROM Employee AS foo INNER JOIN Employee AS bar ON foo.Country = bar.Country
WHERE foo.EmployeeID < bar.EmployeeID ORDER BY foo.EmployeeID, bar.EmployeeID;

-- ---------------------------------------------------------------------------
--                                                                          --
--   Multiple joins can be chained in a single SQL statement.               --
--                                                                          --
-- ---------------------------------------------------------------------------

SELECT 'Now witness the firepower of this fully armed and operational battle station';
SELECT employee.LastName, employee.Country, department.DepartmentName, laptop.Type
    FROM employee LEFT JOIN department
  ON employee.DepartmentID = department.DepartmentID
  LEFT OUTER JOIN laptop
    ON laptop.EmployeeID = employee.EmployeeID;


DROP DATABASE dumb_example;