MySQL - left join? right join? inner?

JOIN's join the data from one table with the data from another.

INNER JOIN

An INNER JOIN, or JOIN, selects all records from the tables specified where the join condition is met.

For example, we have a customers table and an orders table. The customers table has the customers id, first_name and last_name columns, the orders table has the order_date, amount and customer_id columns. If we want to see how many customers have ordered and the date and amount that that order came to, we would have to JOIN the customers and orders tables to get that data.

SELECT first_name, last_name, order_date, amount 
FROM customers
JOIN orders ON orders.customer_id = customers.id;

The JOIN part in the above code block is joining the orders table onto the customer table via (or ON) the customer_id column in the orders table and the id column in the customers table.

This code block will return the first_name, last_name, order_date and amount of all the customers who have ordered but won't return any of the customers who don't have orders even though their first and last names are in the customers table or any orders without customers. This is because an INNER JOIN, or you can just use JOIN, will only take data from both tables where there is relevant data in both tables.

JOIN's select data from two tables where there is a match with both.

LEFT JOIN

A LEFT JOIN selects all records from the left table and any matching records from the right table.

The table on the left is the table specified after the FROM keyword.

The table on the right is the table specified after the JOIN keyword.

SELECT first_name, last_name, order_date, amount 
FROM customers 
LEFT JOIN orders ON orders.customer_id = customers.id;

In the above query the customers table will be the table on the left and the orders table will be the table on the right.

This query will return every customers first and last name and the date and amount their orders came to. If a customer hasn't ordered there will be NULL values placed in the order_date and amount columns for that customer.

LEFT JOIN takes everything from the left table (the table specified after the FROM keyword), and takes any overlapping data from the right table (the table specified after the JOIN keyword)

RIGHT JOIN

Selects all records from the right table and any matching records from the left table.

The table on the right is the table specified after the JOIN keyword.

The table on the left is the table specified after the FROM keyword.

SELECT first_name, last_name, order_date, amount 
FROM customers 
RIGHT JOIN orders ON orders.customer_id = customers.id;

In the above query the customers table will be the table on the left and the orders table will be the table on the right.

This query will return every orders order_date and amount and that orders customers first and last name. If an order doesn't have a customer there will be NULL values placed in the first_name and last_name columns for that order.

RIGHT JOIN takes everything from the right table (the table specified after the JOIN keyword), and takes any overlapping data from the left table (the table specified after the FROM keyword)