Understanding the Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL

Mysql Sql Join Inner Join Outer Join

When working with databases, it is common to have multiple tables that need to be combined or joined together to extract the desired information. In MySQL, there are several types of joins that can be used for this purpose. The most commonly used join types are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

INNER JOIN

The INNER JOIN is the most straightforward type of join. It selects records that have matching values in both tables being joined. It returns only the rows where the join condition is satisfied.

Let's say we have two tables, "customers" and "orders", and we want to retrieve the information of customers who have placed orders. We can use the INNER JOIN to achieve this:

SELECT customers.customer_id, customers.customer_name, orders.order_id 
FROM customers 
INNER JOIN orders ON customers.customer_id = orders.customer_id;

This query will return the customer ID, customer name, and order ID for all customers who have placed orders.

LEFT JOIN

The LEFT JOIN returns all records from the left table (the table that appears before the LEFT JOIN keyword), and the matching records from the right table (the table that appears after the LEFT JOIN keyword). If there is no match, NULL values are returned for the columns of the right table.

Let's consider the same "customers" and "orders" tables. If we want to retrieve all customers, including those who have not placed any orders, we can use the LEFT JOIN:

SELECT customers.customer_id, customers.customer_name, orders.order_id 
FROM customers 
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

This query will return the same results as the INNER JOIN query but will also include customers who have not placed any orders, with NULL values for the order ID.

RIGHT JOIN

The RIGHT JOIN is similar to the LEFT JOIN, but it returns all records from the right table and the matching records from the left table. If there is no match, NULL values are returned for the columns of the left table.

Continuing with the same example, if we want to retrieve all orders, including those placed by customers who are not present in the "customers" table, we can use the RIGHT JOIN:

SELECT customers.customer_id, customers.customer_name, orders.order_id 
FROM customers 
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

This query will return the same results as the INNER JOIN query but will also include orders placed by customers who are not present in the "customers" table, with NULL values for the customer ID and customer name.

FULL JOIN

The FULL JOIN returns all records when there is a match in either the left or the right table. If there is no match, NULL values are returned for unmatched columns.

Building on the previous example, if we want to retrieve all customers and all orders, regardless of any matches, we can use the FULL JOIN:

SELECT customers.customer_id, customers.customer_name, orders.order_id 
FROM customers 
FULL JOIN orders ON customers.customer_id = orders.customer_id;

This query will return all customers and all orders, including those that do not have a match in the other table, with NULL values for unmatched columns.

Conclusion

Understanding the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN is essential when writing SQL queries that involve multiple tables. By choosing the appropriate join type, you can retrieve the desired information from your database efficiently.

Remember:

  • INNER JOIN returns matching records from both tables.
  • LEFT JOIN returns all records from the left table and matching records from the right table.
  • RIGHT JOIN returns all records from the right table and matching records from the left table.
  • FULL JOIN returns all records when there is a match in either the left or the right table.

Related Questions