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
   • LEFT [OUTER] JOIN
   • 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.

CREATE DATABASE Entrepreneur;

CREATE TABLE VatsaCompany (
    V_ID INT NOT NULL PRIMARY KEY,
    V_Name VARCHAR(100) NOT NULL,
    V_Address VARCHAR(250),
    V_ContactNo BIGINT,
    V_TurnOver BIGINT
);

CREATE TABLE Client (
    C_ID INT NOT NULL PRIMARY KEY,
    C_Name VARCHAR(100) NOT NULL,
    C_ContactNo BIGINT,
    C_ClientID INT NOT NULL
);

Now, insert some dummy data into it.

INSERT INTO VatsaCompany VALUES
(2225603,'VatsaPvt.Ltd.','VatsaColony',9876543210,90000000),
(2225604,'VatsaEnclavePvt.Ltd.','VatsaColony',9876543211,98000000),
(2225605,'VatsaSociety','VatsaColony',986543210,90880000),
(2225606,'OSVMall','VatsaColony',9886543210,78800000),
(2225607,'IndianVatsaOilPvt.Ltd.','VatsaColony',9876543810,889800000),
(2225608,'IndianVatsaAirlines','ABCXYZ',9876543810,8080000);

INSERT INTO Client VALUES
(3101,'StephenRock',9876543277,2225603),
(3102,'NilBush',9876543771,2225604),
(3103,'MISSShikha',986573210,2225605),
(3104,'RohitSharma',9886773210,2225606),
(3105,'JohnMax',9876663810,2225607),
(3106,'JohnMaxBhupa',9876666810,2325607),
(3107,'JohnBagh',9877546660,2325607),
(3108,'JohnBagha',9877543666,2325607);

Let’s check our tables by typing the following queries.

SELECT * FROM entrenpreneur.vatsacompany;

 

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

SELECT * FROM vatsacompany v 
INNER JOIN client c
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

SELECT * FROM vatsacompany v 
LEFT JOIN client c
ON v.V_ID=c.C_ClientID

Example 2

In the following example, we are using Order BY Clause with LEFT OUTER Join.

SELECT * FROM vatsacompany v
LEFT JOIN client c
ON v.V_ID=c.C_ClientID
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

SELECT * FROM vatsacompany v 
RIGHT JOIN client c
ON v.V_ID=c.C_ClientID

Example 2

In the following example, we are using Order BY Clause with RIGHT OUTER Join.

SELECT * FROM vatsacompany v 
RIGHT JOIN client c
ON v.V_ID=c.C_ClientID
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

SELECT * FROM vatsacompany v 
CROSS JOIN clientc

Example 2

SELECT * FROM vatsacompany v 
CROSS JOIN client c
ORDER BY V_Name

+

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.