[MySQL] Day 12. JOINS (INNER JOIN, LEFT JOIN, RIGHT JOIN)

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

We will take a look into INNER JOIN, LEFT JOIN, and RIGHT JOIN. 

 

Continued from the previous post, 

customers table consists of 3 columns: customer_id (PRIMARY KEY), first_name, last_name

transactions table consists of 3 columns: transaction_id (PRIMARY KEY), amount, customer_id (FOREIGN KEY)

INSERT INTO transactions (amount, customer_id)
VALUES 	(4.99, 3), 
        (2.89, 2),
        (3.38, 3),
        (4.99, 1),
        (1.00, NULL);

SELECT * FROM transactions;

 

And customers table looks like this: 

INSERT INTO customers (first_name, last_name)
VALUES ("Puffy", "Puff");

SELECT * FROM customers;

 

Again, customer_id column in customers table is the PRIMARY KEY, and it's also referenced as a FOREIGN KEY for customer_id column in transactions table.

 

 

INNER JOIN

Let's start from INNER JOIN.

If you apply INNER JOIN, MySQL selects record that have matching values in both tables.

To perform an INNER JOIN to transactions and customers table, the syntax is as following: 

SELECT * 
FROM transactions INNER JOIN customers
ON transactions.customer_id = customers.customer_id;

-- SELECT *
-- FROM left_table INNER JOIN right_table
-- ON left_table.foreign_key_column = right_table.primary_key_column;

 

Notice that the row with transaction_id = 1004 from transactions table and the row with the name "Puffy" "Puff" in customers table is not included in the INNER JOIN table, because those rows doesn't share common customer_id data in both table. 

 

When using JOINS caluse, we can specify which columns to show as we would do for other SELECT queries. 

SELECT transaction_id, amount, first_name, last_name
FROM transactions INNER JOIN customers
ON transactions.customer_id = customers.customer_id;

 

 

Now, let's look into LEFT JOIN and RIGHT JOIN. 

 

LEFT JOIN & RIGHT JOIN

Starting from LEFT JOIN, it selects every rows from the left table, and join it with matching data from the right table if matching data exists. 

SELECT *
FROM transactions LEFT JOIN customers
ON transactions.customer_id = customers.customer_id;

 

For INNER JOIN, transaction_id 1004 couldn't be found because the customer_id was NULL, and there is no matching NULL data for customer_id in customers table since it's a PRIMARY KEY, meaning it can't have a NULL value. 

But in this case, we can see 1004 in transaction_id column since we used LEFT JOIN.

It chose every single rows from transactions, joining with matching data from customers table. 

Since there is no matching data for transaction_id = 1004, all of the joined data shows NULL. 

 

 

On the other hand, RIGHT JOIN does the opposite, selecting every rows from the right table, joining with matching values from the left table. 

SELECT *
FROM transactions RIGHT JOIN customers
ON transactions.customer_id = customers.customer_id;

 

In this case, we got customer_id = 4 row from customers table whose name is "Puffy Puff", which does not appear from either INNER JOIN or LEFT JOIN. 

But since there is no matching data for customer_id = 4 in transactions table, the joined data (transactions_id, amount, customer_id from transactions table) are all NULL data.