Left Join - (MySQL Join)

In MySQL, a left join is a type of join that returns all the rows from the left table, along with matching rows from the right table (if any). In this tutorial, we'll discuss the syntax and use of left join in MySQL.

Syntax

The syntax for a left join in MySQL is as follows:

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

In this syntax, "table1" is the left table, and "table2" is the right table that we want to join with the left table. The "ON" clause specifies the join condition between the two tables.

Example

Let's say we have two tables, "orders" and "customers". Here's how we can use a left join to combine the data from those two tables:

SELECT *
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.customer_id;

In this example, we are selecting all the columns from both the "orders" and "customers" tables and joining them on the "customer_id" column. This will return all the rows from the "orders" table, along with their corresponding customer data from the "customers" table, if any.

Output

When we run the example query above, the output will look something like this:

order_id	customer_id	order_total	customer_id	customer_name	customer_email
1	1	100	1	John Doe	john@example.com
2	2	200	2	Jane Doe	jane@example.com
3	3	150	null	null	null

In this output, we can see that all the rows from the "orders" table are returned, along with their corresponding customer data from the "customers" table where there is a match. Where a customer doesn't exist for an order, the result shows NULL values for the customer's data.

Explanation

In the example above, we used a left join to combine two tables, "orders" and "customers", on their common column, "customer_id". We selected all the columns from both tables using the "*" wildcard symbol, which returned all the columns from both tables in the output.

The left join returned all the rows from the "orders" table, whether or not there was a matching row in the "customers" table. When a matching row was found in the "customers" table, the corresponding customer data was added to the result set. When there was no matching row found in the "customers" table, NULL values were returned for the customer's data.

Use

The left join is commonly used to combine data from two tables in MySQL. It is useful in cases where we want to include all the rows from one table, even if there is no matching row in the other table.

Important Points

• The left join returns all the rows from the left table.
• The returned rows will include NULL values for the columns from the right table where there is no matching row.
• The left join can be used with any number of tables, not just two.

Summary

In this tutorial, we discussed the left join in MySQL. We covered the syntax, example, output, explanation, use, and important points of left join in MySQL. With this knowledge, you can now use the left join in MySQL to combine data from multiple tables.