Now we all know that in MySQL, we have the concept of LEFT JOIN where the following query will join a table name from the left side with the table queried on the right side.
Here, the joining works in the way of all the left table content will be displayed and the right table content will be displayed with the respective matching pair of the left table content thus called the LEFT JOIN.
So enough said we will be doing a left join on multiple tables and see how that works out.
So today we will be using 3 tables orders, order_items, products for real-time examples to query out our needs out of these tables.
So here is what we are gonna do today:
- We will be fetching products, their quantity, unit price, and shipped date out these 3 tables using the left join.
So let us dive straight into it then.
So let us perform the left join of these 3 tables in total and see how it comes out first.
select * from products p left join order_items oi ON oi.product_id = p.product_id left join orders o ON oi.order_id=o.order_id
- So here we have left joined products table to order_items under the common column of product_id and left joined that order_items to orders table under the common column of order_id.
- From the pic, we can conclude that all the records about the left side of the join are visible and the right side is displayed respectively to the matching columns.
Ok from this table let us filter out what we need which is the products and their quantity and their price and also their shipped date.
select p.name AS Product_name, p.quantity_in_stock AS Stock_Quantity, oi.quantity AS Order_Quantity, oi.unit_price AS Price,o.shipped_date from products p left join order_items oi ON oi.product_id = p.product_id left join orders o ON oi.order_id=o.order_id
- So from here, we have taken out the Products along with their order quantity and price along with shipped date as we discussed using the LEFT join successfully.