MySQL Tutorial: Table Joins (Inner, Left, Right, and Outer Joins) - 2015

A SQL join clause combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.

k@laptop:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.5.40-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| owncloud           |
| performance_schema |
| phpmyadmin         |
+--------------------+
5 rows in set (0.04 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

With Ctrl+D, we can exit from mysql>

mysql> Bye
$

We can create a new database using the following command:

$ mysqladmin -u root -p create TUTORIALS
Enter password: 

Let's go back into mysql console and check if it's been created:

$ mysql -u root -p 
...

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| TUTORIALS          |
| mysql              |
| owncloud           |
| performance_schema |
| phpmyadmin         |
+--------------------+
6 rows in set (0.00 sec)

mysql> 

Yes, it's there!

Let's create two tables:Customers & Orders:

mysql> use TUTORIALS
Database changed
mysql> CREATE TABLE Customers(
    -> CustomerID INT NOT NULL AUTO_INCREMENT,
    -> CustomerName VARCHAR(100) NOT NULL,
    -> ContactName VARCHAR(10) NOT NULL,
    -> Country VARCHAR(40) NOT NULL,
    -> PRIMARY KEY (CustomerID)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TABLE Orders(
    -> OrderID INT NOT NULL,
    ->  CustomID  INT references Customers(CustomerID),
    -> OrderDate INT NOT NULL,
    -> PRIMARY KEY(OrderID)
    -> );
Query OK, 0 rows affected (0.09 sec)

Let's check the tables we created:

mysql> show tables;

+---------------------+
| Tables_in_TUTORIALS |
+---------------------+
| Customers           |
| Orders              |
+---------------------+
2 rows in set (0.01 sec)

First, we want to check the schema before we insert data into our table:

mysql> desc Customers;

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| CustomerID   | int(11)      | NO   | PRI | NULL    | auto_increment |
| CustomerName | varchar(100) | NO   |     | NULL    |                |
| ContactName  | varchar(10)  | NO   |     | NULL    |                |
| Country      | varchar(40)  | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Let's fill in data into Customers table:

mysql> INSERT INTO Customers
    -> (CustomerID, CustomerName, ContactName, Country)
    -> VALUES
    -> (1, "John Chambers", "JC", "US");
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO Customers
    -> (CustomerID, CustomerName, ContactName, Country)
    -> VALUES
    -> (2, "Ana Pushkova", "AP", "Russia");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Customers
    -> (CustomerID, CustomerName, ContactName, Country)
    -> VALUES
    -> (3, "Antonia Jussepe", "AJ", "Italy");
Query OK, 1 row affected (0.06 sec)

Check the data for the Customers table:

mysql> SELECT * FROM Customers;
+------------+-----------------+-------------+---------+
| CustomerID | CustomerName    | ContactName | Country |
+------------+-----------------+-------------+---------+
|          1 | John Chambers   | JC          | US      |
|          2 | Ana Pushkova    | AP          | Russia  |
|          3 | Antonia Jussepe | AJ          | Italy   |
+------------+-----------------+-------------+---------+
3 rows in set (0.00 sec)

For the Orders table:

mysql> desc Orders;

+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| OrderID   | int(11) | NO   | PRI | NULL    |       |
| CustomID  | int(11) | YES  |     | NULL    |       |
| OrderDate | int(11) | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO Orders
    -> (OrderID, CustomID, OrderDate)
    -> VALUES
    -> (334, 19, 1810);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Orders
    -> (OrderID, CustomID, OrderDate)
    -> VALUES
    -> (308, 2, 1990);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Orders
    -> (OrderID, CustomID, OrderDate)
    -> VALUES
    -> (409, 27, 2001);
Query OK, 1 row affected (0.04 sec)

We may want to check the data in the Orders table:

mysql> SELECT * FROM Orders;
+---------+----------+-----------+
| OrderID | CustomID | OrderDate |
+---------+----------+-----------+
|     308 |        2 |      1990 |
|     334 |       19 |      1810 |
|     409 |       27 |      2001 |
+---------+----------+-----------+
3 rows in set (0.00 sec)

join:

mysql> SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    -> FROM Orders
    -> JOIN Customers
    -> ON Orders.CustomID=Customers.CustomerID;

+---------+--------------+-----------+
| OrderID | CustomerName | OrderDate |
+---------+--------------+-----------+
|     308 | Ana Pushkova |      1990 |
+---------+--------------+-----------+
1 row in set (0.00 sec)

Left join:

mysql> SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders LEFT JOIN Customers 
ON Orders.CustomID=Customers.CustomerID;

+---------+--------------+-----------+
| OrderID | CustomerName | OrderDate |
+---------+--------------+-----------+
|     308 | Ana Pushkova |      1990 |
|     334 | NULL         |      1810 |
|     409 | NULL         |      2001 |
+---------+--------------+-----------+
3 rows in set (0.00 sec)

Right join:

mysql> SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate 
FROM Orders RIGHT JOIN Customers 
ON Orders.CustomID=Customers.CustomerID;
+---------+-----------------+-----------+
| OrderID | CustomerName    | OrderDate |
+---------+-----------------+-----------+
|    NULL | John Chambers   |      NULL |
|     308 | Ana Pushkova    |      1990 |
|    NULL | Antonia Jussepe |      NULL |
+---------+-----------------+-----------+
3 rows in set (0.00 sec)

No FULL JOINS on MySQL!