MySQL Joins

                                        MySQL Joins

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:

Orders

Then, look at a selection from the “Customers” table:

Customers

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
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Supported Types of Joins in MySQL

INNER JOIN

Compares all rows from all tables specified in a query with each other and returns records with matching values as a new result table.

LEFT OUTER JOIN

Returns the result table with all rows from the left table and only those rows from the right table that match the JOIN condition (non-matching rows are returned with NULLs).

RIGHT OUTER JOIN

Returns the result table with all rows from the right table and only those rows from the left table where the JOIN condition is fulfilled (non-matching rows are returned
with NULLs).

CROSS JOIN

Combines each row from one table with each row from another table and thus returns a new result table with all possible row combinations from each table.

FULL OUTER JOIN

MySQL does not directly support FULL OUTER JOIN that returns both matching and non-matching rows from the joined tables, but you can combine LEFT and RIGHT OUTER JOINs to achieve the same result.

SELF JOIN

Compares a row with other rows within the same table or extracts hierarchical data — table aliases should be used instead of repeating the same table name in a query.