How does inner, left, right, full join work?
When and what is a JOIN?
Querying data in your database from only one table can only get you so far, sometimes one table just doesn't contain all the data you need. Your current table product may be referencing a foreign key in another table called user by an user ID. If you want to see which product is purchased by who then just querying the product would not be enough.
That's where JOIN is needed it will pull in the data from both table in this case product and user in order to tell you who purchased a particular product.
Dataset
Let's take a look at the current dataset that we will be working with to do the test.
We can see that the customers table have customerNumber as it's primary key and it is used used by other tables have a foreign key such as payments and orders table.
If say we would like to find out the name of the customer who had orders, we cannot just query the order table right? We will only get their customerNumber, which doesn't tell us the name. Yes we can look it up again in the customers table but wouldn't it be easier if you could just query it together?
That's where join come into play.
Inner join / Join
By default in MySQL if you specify join it default to an inner join, so writing inner join / join will have the same effect.
The query:
SELECT customerName, orderNumber FROM customers JOIN orders ON customers.customerNumber = orders.customerNumber LIMIT 5;Results in:
+--------------------+-------------+
| customerName | orderNumber |
+--------------------+-------------+
| Atelier graphique | 10123 |
| Atelier graphique | 10298 |
| Atelier graphique | 10345 |
| Signal Gift Stores | 10124 |
| Signal Gift Stores | 10278 |
+--------------------+-------------+Which tells tells the order number and the customer who had ordered it. Which is great!
Those customers that doesn't have any order are ignored thus those rows are not displayed. For example if we have a customer Alice Bob who has never ordered anything, then we will not see Alice Bob in the queried result because she had never ordered anything.
Left join
Sometimes if you want to keep all records from the left table, in this case the customers table, even if they didn't have a match on the right table i.e. ordered anything like Alice Bob, then you would want to use a left join.
Left join will keep all those records that doesn't match the ON condition and displayed them in the result query.
SELECT customerName, orderNumber
FROM customers
LEFT JOIN orders ON customers.customerNumber = orders.customerNumber
WHERE orderNumber IS NULL;Example result , as you can see all of the entries from the customers table are shown even if they don't satisfy the condition
+--------------------------------+-------------+
| customerName | orderNumber |
+--------------------------------+-------------+
| Havel & Zbyszek Co | NULL |
| American Souvenirs Inc | NULL |
| Porto Imports Co. | NULL |
| Asian Shopping Network, Co | NULL |
| Nat�rlich Autos | NULL |
| ANG Resellers | NULL |
| Messner Shopping Network | NULL |
| Franken Gifts, Co | NULL |
| BG&E Collectables | NULL |
| Schuyler Imports | NULL |
| Der Hund Imports | NULL |
| Cramer Spezialit�ten, Ltd | NULL |
| Asian Treasures, Inc. | NULL |
| SAR Distributors, Co | NULL |
| Kommission Auto | NULL |
| Lisboa Souveniers, Inc | NULL |
| Precious Collectables | NULL |
| Stuttgart Collectable Exchange | NULL |
| Feuer Online Stores, Inc | NULL |
| Warburg Exchange | NULL |
| Anton Designs, Ltd. | NULL |
| Mit Vergn�gen & Co. | NULL |
| Kremlin Collectables, Co. | NULL |
| Raanan Stores, Inc | NULL |
+--------------------------------+-------------+In this case, this result query will be displaying customers who had never gotten any order, those columns from the orders table will just be NULL for them. A good visual is displayed:
Similarly, if we want to show all the orders without any customers, could be possible if the customer paid in cash so you wouldn't know who she / he is.
The customers columns would just be NULL which is very similar to LEFT JOIN.
SELECT customerName, orderNumber
FROM customers
RIGHT JOIN orders ON customers.customerNumber = orders.customerNumber
WHERE customerName IS NULL;In this dataset, because every order has a customer it will be an empty query result. If you remove the WHERE clause it will just be the same as a inner join.
Full [outer] join
Now full join is a combination of basically the left, right, and inner join. You will show records that matches the ON condition, include results that doesn't match the condition from the left table, and incldue results that doesn't match the condition from the right table.
In MySQL there is no full join, so you have to emulate it by using UNION keyword:
SELECT customerName, orderNumber
FROM customers
LEFT JOIN orders ON customers.customerNumber = orders.customerNumber
UNION
SELECT customerName, orderNumber
FROM customers
RIGHT JOIN orders ON customers.customerNumber = orders.customerNumber
Cross join
Cross join in SQL is basically the cartesian product between the two tables. For every row in the first table you will pair it with every row in the second table, combining the row to produce rows in the final table.
In the final query result you would get (the number of rows in first table) x (the number of rows in the second table) rows.
Fun fact, in theory you would obtain inner join by first taking the cross join between the two tables. Then according to the condition you would just throw away all the rows that doesn't match the ON condition. However, in practice it is optimized by the database system to obviously speed up the process.
No Comments