Types of MySQL Joins | MySQL Joins [With Syntax]

Introduction

MySQLis an open-source Relational Database Management System (RDBMS) backed by Oracle. MySQL is made up of  two words- “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

The word Relational in RDBMS is the fundamental on which JOINS in MySQLwork. There can be many types and numbers of tables in any Database. JOINS as the name suggests link tables together using common columns. One  basic example is, Table 1 has employee details and Table 2 has project details.

If we have to check the projects an employee is involved in, we can join the tables on employee name or employee id, hence doing so will join the tables and the new virtual table (can be saved) can be accessed with all the information required. 

Features

  • MySQL is Open-Source and a proper RDBMS to access and manage records of the tables.
  • It supports SQL coding language, which is very common in the field of database management.
  • MySQL is basic and easy to use. It has overwhelming amounts of documentation if someone is stuck at a particular problem.
  • MySQL has a very strong security layer. Passwords are encrypted hence sensitive data is safe from intruders.
  • MySQL is scalable, because it supports multi-threading. Multi-Threading is responsible for fast query execution and data manipulation even if the table is very big in size. 
  • MySQL has Client / Server architecture. There is a Database server and many clients (application programs) that are connected to the server.

Types of Joins

Inner Join

Inner join is the default join type in MySQL. It joins and returns matching records from the two tables. It will compare each row of one table with another and check for the join condition. If the condition is met, a new row is created with the columns from both tables and this new row is included in the output. 

Code Snippet:

SELECT t1.emp_id,t1.emp_name,t2.project_name           

FROM employee as t1

INNER JOIN project as t2         

ON t1.project_id = t2.project_id

In this query, there are two tables. First table consists of details of employees and second comprises all the projects currently going on. After running the above query, MySQL will return all the employees with their respective project details. Here the project_id column is a foreign key in the employee table and primary key in the projects table. Employees without any project_id or project_id without any employee will not be returned.

Left Join

Left join, joins and returns matching records from the two tables and the unmatched records from the first table. Left join essentially returns the output of inner join + unmatched records from the first table. These unmatched rows will also be having the columns from table 2 but the value in those columns will be null as there’s no matching data for those.

Code Snippet:

SELECT t1.emp_id,t1.emp_name,t2.project_name           

FROM employee as t1

LEFT JOIN project as t2         

ON t1.project_id = t2.project_id

In this query, there are two tables. First table consists of details of employees and second comprises all the projects currently going on. After running the above query, MySQL will return all the employees with their respective project details and also the employee details having no project_id assigned. Here the project_id column is a foreign key in the employee table and primary key in the projects table.

Read: Rename Column Name in SQL

Right Join

Right join is very similar to left join, just that now the unmatched records from the second table will be included in the output and only the matching records from the first table. 

Code Snippet:

SELECT t1.emp_id,t1.emp_name,t2.project_name           

FROM employee as t1

RIGHT JOIN project as t2         

ON t1.project_id = t2.project_id

After running the above query, MySQL will return all the employees with their respective project details and also the details of the projects having no matching employees. The employee details columns will be NULL in those cases.

Full Join

As the name suggests, full join returns all the matched and unmatched records. If MySQL is able to find matching rows on the desired column, those records will be matched. The rest of the rows will have NULL values.

Code Snippet:

SELECT *          

FROM employee as t1

FULL OUTER JOIN project as t2         

ON t1.project_id = t2.project_id

After running the above query, MySQL will return all the employees with their respective project details, also the details of employees having no projects and the projects having no matching employees. Certain columns will be having NULL values accordingly.

Cross Join

As the name suggests, cross join will join each row of table 1 with every row of table 2. Irrespective of matching columns. Hence if there are 5 records in each table, the output will be having 25 rows as every row is matched with another row.

Code Snippet:

SELECT *          

FROM employee as t1

CROSS JOIN project as t2

After running the above query, MySQL will return all the employees with all the project details. Although it doesn’t make much sense to visualize this information, but Cross Joins are important and needed in some special and specific cases.

Also Read: SQL Interview Questions & Answers

Conclusion 

JOINS are an important part of Data Extraction. They help combine data from various tables according to the needs of the user. The only con of JOINS can be that they get complicated to write sometimes. At the end of the day JOINS are very useful and various types of joins can be implemented in MySQL. 

All in all, learning and getting hands-on practice on all the databases mentioned in this article will add to your experience. Go through the course overview, learning, and job opportunities involved with it—platforms like upGrad offer power-packed courses designed by the highly reputed institutes like IIIT-B in Full Stack Development.

Prepare for a Career of the Future

UPGRAD AND IIIT-BANGALORE'S PG DIPLOMA IN FULL STACK SOFTWARE DEVELOPMENT
Learn More

Leave a comment

Your email address will not be published. Required fields are marked *

×