MySQL Joining Tables

In MySQL, the JOIN clause is used to combine rows from two or more tables based on a related column between them. This is particularly useful when you need to extract data that is spread across multiple tables but is logically connected.

Example Scenario: Orders and Customers

Imagine you have two tables: Orders and Customers. The Orders table has a column CustomerID, which refers to the CustomerID column in the Customers table. The relationship between these two tables is established through the CustomerID.

Basic INNER JOIN Example

If you want to retrieve orders along with customer details, you would use an INNER JOIN to combine rows that have matching values in both tables.

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

Explanation:

• INNER JOIN returns only the records that have matching values in both tables. If an order doesn't have a corresponding customer, it will not be included in the result.

Supported Types of Joins in MySQL

1. INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Example:

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

• This query will only return orders that have a matching CustomerID in both the Orders and Customers tables.

2. LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table (the first table), and the matched records from the right table (the second table). If no match is found, the result is NULL on the side of the right table.

Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Example:

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

• This query will return all customers, including those who have no orders. If a customer has no matching orders, OrderID will be NULL.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table (the second table), and the matched records from the left table (the first table). If no match is found, the result is NULL on the side of the left table.

Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Example:

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

• This query will return all customers, including those with no orders. If a customer has no corresponding orders, OrderID will be NULL.

4. CROSS JOIN

The CROSS JOIN keyword returns the Cartesian product of the two tables. It returns all possible combinations of rows from both tables. Each row from the first table is combined with all rows from the second table.

Syntax:

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;

• This query will return every possible combination of CustomerName and OrderID from the Customers and Orders tables. The number of rows returned is the product of the number of rows in each table.

Summary of Join Types

Conclusion

Joins are an essential part of relational databases, allowing you to retrieve data from multiple tables based on logical relationships between them. Understanding the different types of joins helps you choose the right one depending on the data you need to extract and how you want to combine it.