MySQL JOINS are used with SELECT statement. It is used to retrieve data from multiple tables. It is performed whenever you need to fetch records from two or more tables.
What are JOINS?
Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Note: JOIN is the most misunderstood topic amongst SQL leaners. For sake of simplicity and ease of understanding , we will be using a new Database to practice sample
MySQL Joining Tables
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let’s look at a selection from the “Orders” table:
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 10308 | 2 | 1996-09-18 |
| 10309 | 37 | 1996-09-19 |
| 10310 | 77 | 1996-09-20 |
Then, look at a selection from the “Customers” table:
| CustomerID | CustomerName | ContactName | Country |
|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mexico |
Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column. Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate<br>FROM Orders<br>INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
and it will produce something like this:
| OrderID | CustomerName | OrderDate |
|---|---|---|
| 10308 | Ana Trujillo Emparedados y helados | 9/18/1996 |
| 10365 | Antonio Moreno Taquería | 11/27/1996 |
| 10383 | Around the Horn | 12/16/1996 |
| 10355 | Around the Horn | 11/15/1996 |
| 10278 | Berglunds snabbköp | 8/12/1996 |
Supported Types of Joins in MySQL
INNER JOIN: Returns records that have matching values in both tablesLEFT JOIN: Returns all records from the left table, and the matched records from the right tableRIGHT JOIN: Returns all records from the right table, and the matched records from the left tableCROSS JOIN: Returns all records from both tables
MySQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the “Orders” table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |
And a selection from the “Customers” table:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
MySQL INNER JOIN Example
The following SQL statement selects all orders with customer information:
Example
SELECT Orders.OrderID, Customers.CustomerName<br>FROM Orders<br>INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the “Orders” table that do not have matches in “Customers”, these orders will not be shown!
JOIN Three Tables
The following SQL statement selects all orders with customer and shipper information:
Example
SELECT ;Orders.OrderID, Customers.CustomerName, Shippers.ShipperName<br>FROM ;((Orders<br>INNER;JOIN;Customers;ON;Orders.CustomerID = Customers.CustomerID)<br>INNER;JOIN;Shippers;ON;Orders.ShipperID = Shippers.ShipperID);
MySQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2).
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;Demo Database
In this tutorial we will use the well-known Northwind sample database. Below is a selection from the “Customers” table:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
And a selection from the “Orders” table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |
MySQL LEFT JOIN Example
The following SQL statement will select all customers, and any orders they might have:
Example
SELECT Customers.CustomerName, Orders.OrderID<br>FROM Customers<br>LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID<br>ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).
MySQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1).
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;Demo Database
In this tutorial we will use the well-known Northwind sample database. Below is a selection from the “Orders” table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |
And a selection from the “Employees” table:
| EmployeeID | LastName | FirstName | BirthDate | Photo |
|---|---|---|---|---|
| 1 | Davolio | Nancy | 12/8/1968 | EmpID1.pic |
| 2 | Fuller | Andrew | 2/19/1952 | EmpID2.pic |
| 3 | Leverling | Janet | 8/30/1963 | EmpID3.pic |
MySQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they might have placed:
Example
SELECT ;Orders.OrderID, Employees.LastName, Employees.FirstName<br>FROM;Orders<br>RIGHT&;JOIN;Employees;ON&;Orders.EmployeeID = Employees.EmployeeID<br>ORDER;BY;Orders.OrderID;
SQL CROSS JOIN Keyword
The CROSS JOIN keyword returns all records from both tables (table1 and table2).
CROSS JOIN Syntax
SELECT column_name(s)
FROM table1
CROSS JOIN table2;Note: CROSS JOIN can potentially return very large result-sets!
Demo Database
In this tutorial we will use the well-known Northwind sample database. Below is a selection from the “Customers” table:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
And a selection from the “Orders” table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |
MySQL CROSS JOIN Example
The following SQL statement selects all customers, and all orders:
Example
SELECT Customers.CustomerName, Orders.OrderID<br>FROM Customers<br>CROSS JOIN Orders;
Note: The CROSS JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in “Customers” that do not have matches in “Orders”, or if there are rows in “Orders” that do not have matches in “Customers”, those rows will be listed as well.
If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause:
Example
SELECT Customers.CustomerName, Orders.OrderID<br>FROM Customers<br>CROSS JOIN Orders<br>WHERE Customers.CustomerID=Orders.CustomerID;
MySQL Self Join
A self join is a regular join, but the table is joined with itself.
Self Join Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;T1 and T2 are different table aliases for the same table.
Demo Database
In this tutorial we will use the well-known Northwind sample database. Below is a selection from the “Customers” table:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
MySQL Self Join Example
The following SQL statement matches customers that are from the same city:
Example
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City<br>FROM Customers A, Customers B<br>WHERE A.CustomerID <> B.CustomerID<br>AND A.City = B.City<br>ORDER BY A.City;
