An Introduction to Joins in MySQL

Introduction

Supported by Oracle company, MySQL is open-source software under the GNU license. It is a Relational Database Management System that depends on Structured Query Language, which is ideal for both small and large applications. This free tool is one of the best RDBMS present in the market. It is scalable, intuitive, and swift that is used to develop scalable web-based software applications. It supports a Client-Server architecture and lies inside the MySQL Server. The MySQL server is responsible for handling all the database instructions, SQL commands, and statements.

This article was published as a part of the Data Science Blogathon.

What are Joins in MySQL?

MySQL databases store vast data in tables. Database Admins (DBAs) and Data Analysts ensure data analysis without loss. MySQL Joins enable retrieval of records from multiple related tables using common keys. This links primary keys to foreign keys, forming relationships like Adhar, PAN, or passport numbers. Foreign keys connect tables. The JOIN clause, used with SELECT, UPDATE, and DELETE, enhances data query efficiency.

Why SQL Joins?

The reason behind the fame of MySQL joins is the ease it provides to extract and collate data from two or more tables. Whenever we have to plot a vast amount of data from the database, we can’t store all data in one table as it takes too much time to load data and data can be inconsistent. That’s why we need to fetch data from different tables. The ultimate goal of DBMS is to achieve normalization which ensures high quality of datasets. The normalization separates the data into multiple tables and the joins clause allows getting data from multiple tables without affecting the data quality and security.

Setting Up a MySQL Table

Before understanding each of them, let’s create two tables:

  CREATE TABLE supplier(
  SNO varchar(4) PRIMARY KEY,
  SNAME varchar(15),
  STATUS int(5),
  CITY varchar(20)
  );
CREATE TABLE parts(
PNO varchar(4) PRIMARY KEY,
SNO varchar(4) REFERENCES supplier(SNO),
PNAME varchar(15),
COLOR varchar(10),
WEIGH int(5),
CITY varchar(20),
cost int(5)
);

Inserting data in supplier and parts table:

Supplier

	INSERT INTO supplier VALUES('S1','Smith',20,'London');
	INSERT INTO supplier VALUES('S2','Jones',10,'Paris');
	INSERT INTO supplier VALUES('S3','Blake',30,'Paris');
	INSERT INTO supplier VALUES('S4','Clark',20,'London');
	INSERT INTO supplier VALUES('S5','Adams',30,'Athens');
	INSERT INTO supplier VALUES('S6','Pavan',24,'Hyderabad');

Output

Parts:

	INSERT INTO parts VALUES('P1','S1','Nut','Red',12,'London',50);
	INSERT INTO parts VALUES('P2','S1','Bolt','Green',17,'Paris',70);
	INSERT INTO parts VALUES('P3','S2','Screw','Blue',17,'Rome',80);
	INSERT INTO parts VALUES('P4','S3','Screw','Red',14,'London',80);
	INSERT INTO parts VALUES('P5','S2','Cam','Blue',12,'Paris',90);
	INSERT INTO parts VALUES('P6','S3','Cog','Red',19,'London',68);

Output:

Types of Joins in MySQL

MySQL supports various types of joins to retrieve data from multiple related tables. The key types of MySQL joins include:

Inner Join

The inner join clause is the most widely used type of MySQL Joins. It is used to retrieve only common matching records or the records from various tables where the join condition is satisfied.

Venn diagram:

Syntax:

SELECT column_names   
FROM tableA   
INNER JOIN tableB  
ON tableA.col = tableB.col;

Example: In our example, we want to fetch data of the supplier with the parts name sold by the Supplier.

select A.SNAME, B.PNAME from supplier A INNER JOIN parts B ON A.SNO=B.SNO;

Output:

Outer Join

With the help of Inner join, you can only retrieve the matched rows, but using Outer join leads to retrieving both the matched and unmatched rows. It will display NULL values for the non-matching rows in a joined table. Types of outer join:

Left Outer Join

LEFT JOIN clause allows retrieving all rows from the left table(Table A), along with those rows from the right table(Table B) for which the join condition is satisfied. Wherever any record of the left table does not match with the right table NULL is displayed for right-side columns.

Venn diagram:

Syntax:

SELECT column_names 
FROM tableA
LEFT [OUTER] JOIN tableB
ON tableA.col = tableB.col;

Example: In our example, we want to fetch data of all the suppliers whether they sold any part or not.

select a.SNAME, a.CITY, b.PNAME, b.COLOR from supplier a LEFT JOIN parts b ON a.SNO=b.SNO;

Output:

Right Outer Join

The RIGHT OUTER JOIN follows the same principle followed by the LEFT OUTER JOIN. It retrieves all the data from the right table(Table B) and matches this data with the records from the left table(Table A). In case, the record in the right table does not have any matching record in the left table, the left table column in the result set will have null values.

Venn diagram:

Syntax:

SELECT column_names
FROM TableA
RIGHT [OUTER] JOIN TableB
ON tableA.col = tableB.col;

Example: In our example, we want to fetch data of parts irrespective of whether they were sold by any supplier or not.

select a.SNAME, a.CITY, b.PNAME, b.COLOR from supplier a RIGHT JOIN parts b ON a.SNO=b.SNO;

Output:

Self Join

Self join implies the joining of a table to itself. It states that each row of a table is joined with itself and with every other row of the same table. When you want to extract the hierarchical data or compare rows within the same table, then self-join is the best choice.

Venn diagram:

Syntax:

SELECT column_name
FROM TableA TA, TableA TB
WHERE condition;

Example: In our example, we want to compare the data of each supplier.

select a.SNAME,b.CITY from supplier a, supplier b where b.SNO=a.SNO;

Output:

Cross Join

MySQL CROSS JOIN or cartesian join helps to retrieve all combinations of rows from each table. If no additional condition is provided, it will return the multiplication of each row of table A with all rows in table B. If the size of table A is m and table B is n then the size of the resultant set will be m*n.

Venn diagram:

Syntax:

SELECT column_names
FROM TableA
CROSS JOIN TableB;

Example: In our example, we want details of every row from both Supplier and Parts tables.

select a.SNAME, a.CITY, b.PNAME, b.COLOR from supplier a CROSS JOIN parts b;

Output:

What are the Limitations of Joins in MySQL?

A most common limitation of using MySQL Joins is that they are quite difficult to read as compared with subqueries. Moreover, it might be confusing to choose a particular type of join to yield the correct desired result. We can’t avoid joins while retrieving data from a normalized database, but it is essential to perform joins correctly because incorrect join operations can lead to inaccurate query results and performance degradation. Another disadvantage is that more joins in a query increase the processing time to retrieve data.

Conclusion

To carry out an insightful analysis, retrieving complex data from a vast set of databases is not easy. The JOIN is one of the most powerful concepts of SQL to do so. This blog has seen the salient aspects of MySQL Join clauses with suitable examples of how they work with queries.

• We discuss the need for joins and their types.
• Learned about the implementation of different types of joins with the help of MySQL queries.
• Also learned some limitations of joins.

Frequently Asked Questions

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Shikha Gupta 25 Aug 2023