Back to: Structured Query Language (SQL)
A JOIN is a clause that is used to combine rows from two or more tables based on a related column between them, such as a foreign key.
If we think of the two tables (customers and transactions) as a Venn diagram – whatever data the two tables have in common is the middle (intersection) of the Venn diagram.
For this example it will be helpful to add a little more data:
INSERT INTO transactions (amount, customer_id)
VALUES (1.00, NULL);
SELECT * FROM transactions
We have added a NULL value here, some users may pay anonymously by cash.
we’ll add a fourth customer to the customer table as well:
INSERT INTO customers (first_name, last_name)
VALUES ("Poppy", "Puff");
SELECT * FROM customers;
Not all transactions have a customer_id and not all customers have ever initiated a transaction. They could be registered as a customer, but my never have made a purchase. Using JOINS we can look at what data these tables have in common.
INNER JOINS
To create an inner join between these tables:
SELECT *
FROM transactions INNER JOIN customers
ON transactions.customer_id = customers.customer_id;
We have created an inner join from these two tables based solely on what they have in common. We do not have a row for the $1 transaction that doesn’t have a customer_id, similarly, there is no row for Poppy Puff as she has not made a purchase and hence has no corresponding customer_id in the transactions table. We are telling MySQL to select all rows from these two tables, that have matching customer_ids. Hence – some data is excluded.
We can select specific columns when executing this type of query:
SELECT transaction_id, first_name, last_name, amount
FROM transactions INNER JOIN customers
ON transactions.customer_id = customers.customer_id;
LEFT JOIN
Using a LEFT JOIN we will display everything from the left table (our transactions). If there is a matching customer_id, we want to pull in any relevant data from the table on the right.
SELECT *
FROM transactions LEFT JOIN customers
ON transactions.customer_id = customers.customer_id;
RIGHT JOIN
Using a RIGHT JOIN, we will display the entire table on the right. If there are any matching rows on the left, we will pull them in. We are displaying ALL of our customer – if they have completed a transaction, it will be displayed, if not it will be ignored.
SELECT *
FROM transactions RIGHT JOIN customers
ON transactions.customer_id = customers.customer_id;
