A JOIN clause is used to combine rows from multiple tables, based on a related column between them.
There are four basic join types : inner, left, right and full
- INNER JOIN : Returns records that have matching values in both tables
- LEFT JOIN : Returns all records from the left table, and the matched records from the right table
- RIGHT JOIN : Returns all records from the right table, and the matched records from the left table
- FULL JOIN : Returns all records when there is a match in either left or right table
EXAMPLES OF SQL JOIN TYPES
Before going into the details, I want to introduce two tables that I’m going to use to demonstrate the examples. The relationship between them is specified by the ‘id’ primary key in the ‘customers’ table and the ‘customer_id’ foreign key in the ‘orders’ table.
INNER JOIN
For instance, if we want to get list of all customers who placed an order and details of the order they placed. Inner Join will be perfect for this demand since Inner Join returns all the records that match values in both tables. The syntax looks like this:
LEFT JOIN
If we just want the information of customers, regardless they placed an order or not, we would use LEFT JOIN which returns records from table left and any matching records from table right
Since Raiden, Hokage and Connie have not placed any order, the ‘amount’ and ‘date’ are NULL, simply means there is no data
RIGHT JOIN
RIGHT JOIN is sort of a opposite version of LEFT JOIN, in this case it would return all the orders with customer infomation attached
FULL JOIN
Finally, the FULL JOIN returns all records from both tables
Since FULL JOIN is not supported by MySQL, but we can emulate them using LEFT JOIN ,RIGHT JOIN and UNION. Try to figure out yourself.
References : w3school
Last Words
Although my content is free for everyone, but if you find this article helpful, you can buy me a coffee here
