MySQL joins are used to combine data from multiple tables in a relational database. They allow you to retrieve information from multiple tables as if they were a single table, making it easier to work with large datasets and perform complex queries.
There are several types of joins in MySQL:
INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and SELF JOIN.
In the queries for each case, we will be using category, film, customer, payment, inventory, rental, and actor. You can view the contents/description of all the tables which we will be using here.
INNER JOIN:
INNER JOIN returns only the rows that have matching values in both tables. It is used when you only want to retrieve data that is common between both tables.
Syntax:
SELECT column_name
FROM table_1
INNER JOIN table_2
ON table_1.column_name = table_2.column_name;
Aim: To find the category of the films.
SELECT category.name, film.title
FROM category
INNER JOIN film_category ON category.category_id = film_category.category_id
INNER JOIN film ON film_category.film_id = film.film_id;
Output:
This query returns the name of categories, and the title of films, where the category_id in the categories table matches the category_id in the film_category table and the film_id in the film_category table matches the film_id in the films table.
LEFT JOIN:
LEFT JOIN returns all rows from the left table and the matching rows from the right table.
If there is no match, the result will contain NULL values.
It is used when you want to include all rows from one table and the matching rows from the other table.
Syntax:
SELECT column_name
FROM table_1
LEFT JOIN table_2
ON table_1.column_name = table_2.column_name;
Aim: To get the names of customers with the sum of active payments.
SELECT customer.first_name, customer.last_name, SUM(payment.amount)
FROM customer
LEFT JOIN payment ON customer.customer_id = payment.customer_id
GROUP BY customer.customer_id;
Output:
This query returns the first name, and last name of customers, and the sum of active payments, where the customer_id in the customer table matches the customer_id in the payments table.
RIGHT JOIN:
RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If there is no match, the result will contain NULL values.
It is used when you want to include all rows from one table and the matching rows from the other table.
Syntax:
SELECT column_name
FROM table_1
RIGHT JOIN table_2
ON table_1.column_name = table_2.column_name;
Aim: To find the return dates of films.
SELECT inventory.film_id, film.title, rental.return_date
FROM inventory
RIGHT JOIN film ON inventory.film_id = film.film_id
RIGHT JOIN rental on inventory.inventory_id = rental.inventory_id;
Output:
This query returns the film_id, the title of films, and the return date of rental, where the film_id in the inventory table matches the film_id in the film table and the inventory_id in the inventory table matches the inventory_id in the rental table.
If an inventory item doesn't have any rental, the return date will be NULL.
CROSS JOIN:
CROSS JOIN returns all possible combinations of rows from both tables.
It is used when you want to include all rows from both tables, regardless of whether there is a match.
Syntax:
SELECT column_name
FROM table_1
CROSS JOIN table_2;
Aim: To find all categories of films where the actor has worked.
SELECT actor.first_name, actor.last_name, category.name
FROM actor
CROSS JOIN category;
Output:
SELF JOIN:
A self-join is when a table is joined with itself. The table is used twice, once as a left table and once as a right table.
The join condition is made by matching the columns of the left table with the columns of the right table.
Syntax:
SELECT column_name(s)
FROM table1 table_alias_1, table1 table_alias_2
WHERE condition;
Aim: Get actors who have acted together in films. (co-actors)
SELECT a.first_name, a.last_name, b.first_name AS co_actor_first_name, b.last_name AS co_actor_last_name
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
JOIN film_actor fb ON fa.film_id = fb.film_id
JOIN actor b ON fb.actor_id = b.actor_id
WHERE a.actor_id != b.actor_id;
Output:
The query returns the first name, and last name of the actors, and the first name, and last name of the actors who have acted in the same films, where it will exclude the same actor.
It is important to consider the performance of your query when using joins, especially when working with large datasets, as joins can slow down your query performance.
In summary, MySQL joins are a powerful tool for retrieving and manipulating data from multiple tables in a relational database. They allow you to perform complex queries and retrieve data from multiple tables as if they were a single table. It is important to choose the right type of join depending on the relationships between the tables, the type of data you want to retrieve, and the performance of the query.
'll meet again tomorrow!
To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.
References: w3schools.com/mysql/default.asp
*****All the outputs provided are a snippet of the actual result.