Introduction to Joins in MySQL

Hi, this is Charu from Classmethod. I used to get very confused while performing Joins in SQL queries. It is just a query representation of Sets in Mathematics. It is a method for retrieving data from two or more tables based on a relationship between certain columns in these tables.

Types Of Joins:

INNER JOIN: Returns rows when there is at least one match in both tables.

LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.

RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.

CROSS JOIN: Returns the Cartesian product of the two tables.

To know more about these 4 Joins, let's go through an example. I got this example from Leetcode.

I will be sharing two tables and will demonstrate all the Joins on the same tables with the help of Venn diagrams. The two circles in the venn diagrams represents Table A and Table B respectively.

Let's get started!

Here is the example,

This is the Sales table, where (sale_id, year) is the primary key (combination of columns with unique values) of this table. product_id is a foreign key (reference column) to Product table(next table). Each row of this table shows a sale on the product product_id in a certain year. Note that the price is per unit.

The above is a Product table, where product_id is the primary key of this table and each row of this table indicates the product name of each product.

Inner Join:

An INNER JOIN selects records that have matching values in both tables.

To get a list of all sales including the product names, we'd use:

SELECT sales.sale_id, sales.product_id, sales.year, sales.quantity, sales.price, product.product_name
FROM Sales
INNER JOIN Product 
ON Sales.product_id = Product.product_id;

The above query will return,

LEFT JOIN:

A LEFT JOIN returns all records from the left table (sales), and the matched records from the right table (product). The result is NULL from the right side if there is no match.

SELECT sales.sale_id, sales.product_id, sales.year, sales.quantity, sales.price, product.product_name
FROM Sales
LEFT JOIN Product 
ON Sales.product_id = Product.product_id;

This will return the same as the INNER JOIN in this case, as all product_ids in the sales table have a matching entry in the product table,

RIGHT JOIN

A RIGHT JOIN returns all records from the right table (product), and the matched records from the left table (sales). The result is NULL from the left side if there is no match.

SELECT sales.sale_id, sales.product_id, sales.year, sales.quantity, sales.price, product.product_name
FROM Sales
RIGHT JOIN Product
ON Sales.product_id = Product.product_id;

This query will return,

CROSS JOIN

A CROSS JOIN returns the Cartesian product of rows from the tables in the join. In other words, it combines each row from the first table with each row from the second table.

SELECT sales.sale_id, sales.product_id, sales.year, sales.quantity, sales.price, product.product_name
FROM Sales
CROSS JOIN Product;

The above query will produce a result set that pairs each sale with every product, which can result in many combinations.

Each sale is paired with every product, resulting in 9 rows, since there are 3 sales and 3 products. ​

Conclusion:

In this blog, we learned about four different types of Joins. Practice and experiment with different examples and expand your knowledge about Joins.

Thank you for reading!

Happy learning:)