An Introduction to Joins in MySQL

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

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.

What is a MySQL Join?

MySQL databases are nothing but a collection of multiple tables that holds a large amount of data. The job of Database Admins(DBAs) and Data Analysts is to analyze the data effectively without any loss. To retrieve the records based on specific conditions from two or more tables stored in a database, they use the concept of MySQL Joins. With the help of MySQL joins, we can pull out the data from multiple tables in a single query. To perform the SQL joins, the tables must be related to each other with a common key field. The most frequent pattern is leveraged to join the primary key of one table to its foreign key. A primary key is the unique identifiers of a table that ensures the data in a specific is column is unique. The real-world example of primary key columns can be Adhar number, PAN number, UAN, passport id, etc. A foreign key is a column or group of columns that are used to link related tables together in a relational database. A column that acts as a primary key in one table can only become the foreign key in another table. The concept of primary and foreign keys is used to relate the multiple tables mutually. Generally, the JOIN clause is used with UPDATE, SELECT, and DELETE statements of MySQL.

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.

Types of Joins

MySQL supports 5 types of joins including Self join, Inner join, Outer(Left outer and right outer) join, and cross join. 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

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:

figure:- https://www.educba.com/joins-in-mysql/

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 in MySQL:

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:

figure:- https://arctype.com/blog/mysql-join/

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:

figure:- https://www.geeksforgeeks.org/postgresql-joins/

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:

figure:- https://codingsight.com/sql-joins-tutorial-with-examples/

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:

figure:- https://www.c-sharpcorner.com/article/sql-joins/

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.

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