Left Join
"Give me everything from the left table and only matching values from the right table"
Lets just use a simple LEFT JOIN query and display everything to see what its giving out.
SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;
The resultant table will look something like this:
| id | name | age | address | order_id | customer_id | item | price | status |
|---|---|---|---|---|---|---|---|---|
| 1 | Nexus | 22 | Cloud | OD1 | 1 | Mechanical Keyboard | 2000 | PENDING |
| 1 | Nexus | 22 | Cloud | OD2 | 1 | Noctua Fan | 1500 | PENDING |
| 2 | Niko | 30 | Liberty City | NULL | NULL | NULL | NULL | NULL |
| 3 | CJ | 24 | Groove Street | NULL | NULL | NULL | NULL | NULL |
| 4 | Tommy | 35 | Vice City | NULL | NULL | NULL | NULL | NULL |
| 5 | Kratos | 1001 | Midgard | OD4 | 5 | Mechanical Keyboard | 2000 | PENDING |
| 5 | Kratos | 1001 | Midgard | OD5 | 5 | Blades of Chaos | 9000 | SHIPPED |
| 6 | Jin Sakai | 30 | Tsushima | NULL | NULL | NULL | NULL | NULL |
| 7 | Gustav | NULL | Lumiere | OD3 | 7 | Mechanical Keyboard | 200 | PENDING |
The table has everything from the left table (customers) but only matching values from the right table (orders). For values that don't match the fields will be NULL.
For example, in our customer table - CJ and Tommy has not placed any orders, They will still appear in the resultant table but the fields of orders table for them will be NULL.
Meanwhile Nexus has 2 orders so he will appear 2 times as for both times there was a match.
Why is LEFT JOIN important ?
Inner Join was pretty self explanatory why it can be important but LEFT JOIN can be useful for data where the data are missing. In bigger Databases its quite the helpful thing.
Key Note
The mental Model to keep is that in LEFT JOIN, no matter what everything from the Left table will be shown regardless !!
Lets Do some questions to Grasp it better:
Q. Display List of all customers and how many orders each have placed.
S. To solve this we can mix Joins, Aggregates and GROUP BY ! But first lets dissect on how we will write the query:
We need to display all the customers -> This gives away which is the left table - customers ! As we know LEFT JOIN always gives out everything from the left table.
Next we need "HOW MANY ORDERS" -> Eazy ! A simple COUNT aggregate ! But doing COUNT(orders.order_id) will count all the orders in the whole resultant table but we want to count orders per customer and to this we can use GROUP BY customers based on their name or id so the aggregate is applied on each group !
SELECT customers.name, COUNT(orders.order_id) AS total_order FROM customers LEFT JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name;
Now a thing to notice, We are grouping by customer.name and it works but lets say in a DB there are 2 customer with the same name and surname then it will group them together but both are different customers that's why the best approach is to GROUP BY customers.id; As both the customers will have different IDs !!
Q. Using LEFT JOIN, list all customers and their most expensive order. If they have no orders, show 0.
S. Again we dissect and figure our step by step !!
List All customers -> So customers table is the left table !
Their Most expensive order -> Hmmmm........most expensive of all means the highest price of all the orders for a customer that means we can do a MAX(orders.price) aggregate ! Now as seen before we will apply a GROUP BY but this time on customers.id so that the MAX() is applied on each group or else it will do a MAX() on the whole table.
And we do a MAX() on orders.order_id because if a order_id exists that means its a valid order and its PK !
HAH ! We solved half of it already !
Now it says if there are no orders show 0, we will see this in query.
SELECT customers.name, COALESCE(MAX(orders.order_id), 0) AS total_spent FROM customers LEFT JOIN orders ON customers.id = orders.customer_id GROUP BY customers.id;
Now that is a big query but we know the whole thing except the COALESCE() function. So its the part which displays 0 if value is NULL.
The COALESCE() Function
The COALESCE() function takes at least 2 arguments and returns the first non-null value. If all the values are NULL then it returns NULL !
So in our case COALESCE(MAX(orders.order_id), 0) will return 0 for those rows which don't have an order !
That was LEFT JOIN !!
Follow Right Join to continue from here.
Browse Full SQL Page.