Joins in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain MySQL Joins with examples. This article will cover the following topics. Let’s see.
  1. Introduction
  2. Joins
  3. Classification
  4. Conclusion

JOINS

 
In the MySQL command, a join is used to select the data from two or more tables or views, based on related columns between them.
 
Note:
MySQL hasn’t supported the FULL OUTER JOIN yet.
 

TYPES OF JOINS IN MYSQL

 
There are three types of Joins in MySQL, viz.
  1. INNER JOINS
  2. OUTER JOINS
    1. LEFT [OUTER] JOIN
    2. RIGHT [OUTER] JOIN
  3. CROSS JOIN
First, let's create a database with a few tables containing some dummy data into it. Here, I am providing the database with the tables containing the records, on which I am showing you the various examples. Let's see.
  1. CREATE DATABASE Entrenpreneur;  
  2.   
  3. CREATE TABLE VatsaCompany(  
  4.     V_ID INT NOT NULL primary key,  
  5.     V_Name Varchar(100) NOT NULL,  
  6.     V_Address Varchar(250),  
  7.     V_ContactNo bigint,  
  8.     V_TurnOver bigint  
  9. );  
  10.   
  11. CREATE TABLE Client(  
  12.     C_ID INT NOT NULL primary key,  
  13.     C_Name Varchar(100) NOT NULL,  
  14.     C_ContactNo bigint,  
  15.     C_ClientID INT NOT NULL  
  16. ); 
Now, insert some dummy data into it.
  1. INSERT INTO VatsaCompany VALUES  
  2.     (2225603, 'Vatsa Pvt. Ltd.''Vatsa Colony', 9876543210, 90000000),  
  3.     (2225604, 'Vatsa Enclave Pvt. Ltd.''Vatsa Colony', 9876543211, 98000000),  
  4.     (2225605, 'Vatsa Society''Vatsa Colony', 986543210, 90880000),  
  5.     (2225606, 'OSV Mall''Vatsa Colony', 9886543210, 78800000),  
  6.     (2225607, 'Indian Vatsa Oil Pvt. Ltd.''Vatsa Colony', 9876543810, 889800000),  
  7.     (2225608, 'Indian Vatsa Airlines''ABCXYZ', 9876543810, 8080000);  
  8.   
  9. INSERT INTO Client VALUES  
  10.     (3101, 'Stephen Rock', 9876543277, 2225603),  
  11.     (3102, 'Nil Bush', 9876543771, 2225604),  
  12.     (3103, 'MISS Shikha', 986573210, 2225605),  
  13.     (3104, 'Rohit Sharma', 9886773210, 2225606),  
  14.     (3105, 'John Max', 9876663810, 2225607),  
  15.     (3106, 'John Max Bhupa', 9876666810, 2325607),  
  16.     (3107, 'John Bagh', 9877546660, 2325607),  
  17.     (3108, 'John Bagha', 9877543666, 2325607); 
Let’s check our tables by typing the following queries.
 
1)
  1. SELECT * FROM entrenpreneur.vatsacompany; 
 
2)
  1. SELECT * FROM entrenpreneur.client; 
 

MySQL INNER JOIN

 
Two tables based on a condition known as a join predicate are joined by the inner join.
 
Each row from the first table is compared with a row from the second table by the inner join clause. If values in both rows cause the join condition to be evaluated as valid, then the inner join clause returns a new row to the final result set. The inner join clause, in other words only contains rows whose values match.
 
Syntax
SELECT <column_names>
FROM <table1>
INNER JOIN <table2> 
ON <Join_condition>;
 
Example

Let’s discuss one example to understand the concept of INNER JOIN
  1. SELECT *  
  2. FROM vatsacompany v INNER JOIN client c  
  3. ON v.V_ID = c.C_ClientID; 
 
Now, try to understand the concept of Inner Join by using the Venn diagram.
 

MySQL OUTER JOIN

 
The concepts of left and right tables are introduced, when we use an outer join.
 
There are two types of outer joins.
  1. LEFT [OUTER] JOIN
  2. RIGHT [OUTER] JOIN

MySQL LEFT OUTER JOIN

 
Whether or not matching rows exist in the right table, the left join selects all rows from the left table. If no corresponding rows are found from the right table, NULL is used in the final result set for the columns of the row from the right table.
 
A left join requires a join-predicate as well.
 
Syntax
SELECT <column_names>
FROM <table1>
LEFT [OUTER] JOIN <table2>
ON <Join_condition>;
 
Let us discuss a few examples to understand the concept of LEFT [OUTER] JOIN.
 
Example 1
  1. SELECT *  
  2. FROM vatsacompany v LEFT JOIN client c  
  3. ON v.V_ID = c.C_ClientID 
 
Example 2
 
In the following example, we are using Order BY Clause with LEFT OUTER Join.
  1. SELECT *  
  2. FROM vatsacompany v LEFT JOIN client c  
  3. ON v.V_ID = c.C_ClientID  
  4. ORDER BY v.V_TurnOver 
 
The following Venn diagram illustrates the concept of Left Outer Join.
 
 
 

MySQL RIGHT OUTER JOIN

 
Except that the handling of tables is reversed, the right join clause is the same as the left join clause. Instead of the left table, it starts selecting data from the right table.
 
Syntax
SELECT <column_names>
FROM <table1>
RIGHT [OUTER] JOIN <table2>
ON <Join_condition>;
 
Example 1
  1. SELECT *  
  2. FROM vatsacompany v RIGHT JOIN client c  
  3. ON v.V_ID = c.C_ClientID 
 
Example 2
 
In the following example, we are using Order BY Clause with RIGHT OUTER Join.
  1. SELECT *  
  2. FROM vatsacompany v RIGHT JOIN client c  
  3. ON v.V_ID = c.C_ClientID  
  4. ORDER BY V.V_TurnOver 
 
The following Venn diagram illustrates the concept of Right Outer Join.
 
 
 

MYSQL CROSS JOIN

 
The cross join clause does not have a join condition, unlike the inner join, left join, and right join.
 
A Cartesian product of rows from the joined tables is produced by the cross join. In order to make the result set, the cross pair combines each row from the first table with each row from the other table.
 
Example 1
  1. SELECT *  
  2. FROM vatsacompany v CROSS JOIN client c 
 
Example 2
  1. SELECT *  
  2. FROM vatsacompany v CROSS JOIN client c  
  3. ORDER BY V_Name 

REFERENCES

 
https://www.mysqltutorial.org/
 

CONCLUSION

 
In this article, I have discussed the concept of Joins in MySQL with various examples.
 
To read more about the installation of MySQL, visit How To Install MySQL On Windows 10.
 
If you want to know about various important queries of MySQL, visit Important Queries In MySQL.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article.