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.