An overview of types of Joins in MySQL

SQL JOIN

A JOIN a clause is used to associate rows from two or more tables, placed on a similar column between them.

With the help of joins, we can recover the data from two or more tables rooted in logical relationships between the tables. Joins signalizes how the  SQL Server is compelled with the use of data from one table to select the rows in another table. Based on the two circumstances such as by specifying the column from each table to be used for the join.

Basically, database tables are connected with each other with keys. We use this key relationship in SQL Joins.

Types of Joins in SQL 

SQL Server we have three types of joins. By using these joins we get the data from multiple tables based on their condition.

Inner Join
Inner join returns only those records that match the tables. In an inner join, we generally use FORM or the WHERE clause in which the data of the first table is merged/joined. By using another table using the terms 'inner join' followed by the second table to be joined with the first table.

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Table name Order

Table name Customers

Example

SELECT Order.OrderId, Customers.Name
FROM Order
INNER JOIN Customers ON Order.CustomerId = Customers.CustomerId;

Example of Joining three table

SELECT Order.OrderId, Customers.Name, Employee.EmployeeName
FROM ((Order
INNER JOIN Customers ON Order.CustomerId = Customers.CustomerId)
INNER JOIN Employee ON Order.EmployeeId = Employee.EmployeeId);

Outer Join

Right outer join is also called an Outer Join. Right, join is used to join more than two tables from the database. In these use-cases, using a right join method is a better choice because it can avoid the relocation of a table through which our whole query joins one table. 
 The right join is exceptionally used due to their entanglement, so for the basic and uncomplicated join, it is better to use a left join than a right. It makes the query accessible and easier and readable by others.

There are three types of Outer Join 

Left Outer Join

Left outer join returns all records from the left table(table 1) and from the right table(table 2) returns only match or similar records. If there are no duplicate/identical columns in the right table, it returns NULL values.

LEFT OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Table name Order

Table name Customers

Example 

SELECT Customers.Name, Order.OrderId
FROM Customers
LEFT JOIN Order
ON Customers.CustomerId=Order.CustomerId
ORDER BY Customers.Name;

Right Outer Join

A right outer join returns all records/rows from the right table( table2 )  and from the left table ( table1 )returns only match or similar records. If there are no duplicate/identical columns in the left table, it returns NULL values.

RIGHT OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Table name Order

Table name Employees

Example

SELECT Order.OrderId, Employees.LastName, Employees.FirstName
FROM Order
RIGHT JOIN Employees
ON Orders.EmployeeId = Employees.EmployeeId
ORDER BY Order.OrderId;

Full Outer Join

Full outer join connects left outer join and right outer join. This join returns all records from both tables. that is (table 1) and ( table 2). If there are no columns duplicated/identical in both tables, it returns NULL values.

FULL OUTER JOIN Syntax

SELECT column_name
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Table name Order

Table name Customers

Example

SELECT Customers.Name, Orders.OrderId
FROM Customers
FULL OUTER JOIN Order ON Customers.CustomerId=Order.CustomerId
ORDER BY Customers.Name;

Self Join
Self-join is used to join a database table virtually or independently, specifically when the table has a Foreign key that indicates its own Primary Key.  We use only three JOINS to join a table to itself that is Inner join, Outer join, and Cross join. . Hence Self-join is not a type of SQL join.

SELF JOIN Syntax

SELECT column_name
FROM table1 T1, table1 T2
WHERE condition;

Table name Customers

Example

SELECT A.Name AS Name1, B.Name AS Name2, A.City
FROM Customers A, Customers B
WHERE A.CustomerId <> B.CustomerId
AND A.City = B.City
ORDER BY A.City;