Joining tables with JOIN

MySQL is one of the most popular database management systems in the world, and one of the most important concepts to understand when working with it is how to join tables using the JOIN command. This is an SQL command that is used to combine rows from two or more tables, based on a related column between them.

There are several types of JOINs available in MySQL: INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN. Each of them has a specific purpose and is used in different scenarios. Let's explore each of them in detail.

INNER JOIN

The INNER JOIN is the most common type of JOIN. It returns rows when there is a match in both tables. If there is no match, the row is not returned.

For example, if we have two tables, 'Customers' and 'Orders', and we want to find all the customers who have placed an order, we could use INNER JOIN to join the two tables based on the customer ID. The syntax would be something like this:

SELECT Customers.Name, Orders.Product
FROM Clients
INNER JOIN Orders
ON Customers.ID = Orders.ID_Customer;

This command would return a table of customer names and the products they ordered. If a customer did not place an order, it would not appear in the resulting table.

Continue in our app.

• Listen to the audio with the screen off.
• Earn a certificate upon completion.
• Over 5000 courses for you to explore!

Or continue reading below...

Download the app

LEFT JOIN

The LEFT JOIN returns all rows from the left table, and the corresponding rows from the right table. If there is no match, the result is NULL on the right hand side.

Using the same example, if we wanted to find all customers, regardless of whether they placed an order or not, we could use LEFT JOIN. The syntax would be something like this:

SELECT Customers.Name, Orders.Product
FROM Clients
LEFT JOIN Orders
ON Customers.ID = Orders.ID_Customer;

This command would return a table with the names of all customers. If a customer placed an order, the product they ordered would be listed. If they didn't place an order, there would be a NULL in the product column.

RIGHT JOIN

The RIGHT JOIN is the opposite of the LEFT JOIN. It returns all rows from the table on the right, and the corresponding rows from the table on the left. If there is no match, the result is NULL on the left.

Using the same example, if we wanted to find all orders, regardless of whether they were placed by a customer or not, we could use RIGHT JOIN. The syntax would be something like this:

SELECT Customers.Name, Orders.Product
FROM Clients
RIGHT JOIN Orders
ON Customers.ID = Orders.ID_Customer;

This command would return a table with all the products that were ordered. If an order was placed by a customer, the customer's name would be listed. If the order was not placed by a customer, there would be a NULL in the customer name column.

FULL JOIN

FULL JOIN returns rows when there is a match in one of the tables. So if there is a row in the first table that has no matching row in the second table, or a row in the second table that has no matching row in the first table, that row will be listed.

Unfortunately, MySQL does not support FULL JOIN directly, but you can achieve the same result using a combination of LEFT JOIN and UNION.

SELECT Customers.Name, Orders.Product
FROM Clients
LEFT JOIN Orders
ON Customers.ID = Orders.ID_Customer
UNION
SELECT Customers.Name, Orders.Product
FROM Clients
RIGHT JOIN Orders
ON Customers.ID = Orders.ID_Customer;

This command would return a table of all customers and all orders. If a customer didn't place an order, there would be a NULL in the product column. If an order was not placed by a customer, there would be a NULL in the customer name column.

In summary, the JOIN command is a powerful tool in MySQL that allows you to join two or more tables based on a related column between them. Understanding how to use the different types of JOINs can help you efficiently extract valuable information from your data.