MySQL Joins

MySQL Joining Tables

A JOIN clause is utilized to merge rows from two or more tables based on a related column between them.

Here is a selection from the “Orders” table:

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Next, let’s examine 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 corresponds to the “CustomerID” in the “Customers” table. The relationship between these two tables is based on the “CustomerID” column.

Next, we can construct the following SQL statement (using an INNER JOIN) to select records that have matching values in both tables:

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

and it will generate output similar to this:

OrderID	ContactName	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: Retrieves records with matching values in both tables.
LEFT JOIN: Retrieves all records from the left table and matched records from the right table.
RIGHT JOIN: Retrieves all records from the right table and matched records from the left table.
CROSS JOIN: Retrieves all combinations of records from both tables.

img_inner_join img_left_join img_right_join

img_cross_join