Sabari M Sabari M
Updated date Jan 19, 2021
SQL Joins are more important in any SQL database. In this blog, we will discuss JOINS and their types and examples. SQL types are Inner Join, Left Join, Right Join, and Full Join.

JOINS in SQL are commands which are used to merge rows from two or more tables, based on a related column between those tables. In SQL different types of JOINS are there, in this blog we will see the types of joins and examples.

Type of Joins

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

Inner Join

The INNER JOIN creates a new result set table by combining all column values of two tables or more (table1 and table2 and so on) based upon the join-predicate. The code snippet compares each row of table1 with each row of table2 to scan for pairs of rows that satisfy the join-predicate.

This type of join displays those records which have matching values in both tables. So, if you perform an INNER join operation between Table 1 and Table 2, all the tuples which have matching values in both the tables will be given as output.

Syntax:

SELECT

    column_name(s)

FROM table1 

INNER JOIN table2

ON table1.column_name = table2.column_name;

Example:

We would like to display on one result set the following columns namely FirstName, LastName, Salary, DOB, Depart Name, Profit, Revenues wherein it will display only records that are both present on both tables.

Reference Table 1: tblEmployees

Reference Table 2: tblSales

Query: (Inner Join)

SELECT
    e.ID,
    e.FirstName,
	e.LastName,
	e.Salary,
	e.DOB,
	s.ID,
	s.DepartmentName,
	s.Profit,
	s.Revenues,
	s.ID_Employee
FROM dbo.tblEmployees e
INNER JOIN dbo.tblSales s
ON e.ID = s.ID_Employee;

Result: (Inner Join)

Left Join

The SQL LEFT JOIN joins two tables or more and retrieves all matching rows of the table on the left side of the join and matching rows for the table on the right side of the join.

Syntax:

SELECT

    column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;

Query: (Left Join)

SELECT
    e.ID,
    e.FirstName,
	e.LastName,
	e.Salary,
	e.DOB,
	s.ID,
	s.DepartmentName,
	s.Profit,
	s.Revenues,
	s.ID_Employee
FROM dbo.tblEmployees e
LEFT JOIN dbo.tblSales s
ON e.ID = s.ID_Employee;

Result: (Left Join)

Right Join

The SQL RIGHT JOIN joins two tables and fetches all the rows of the table on the right side of the join and matching rows for the table on the left side of the join, The reference table will be the second table any ID that the second table contains will be the primary ID reference of the query result.

Syntax:

SELECT

    column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

Query: (Right Join)

SELECT
    e.ID,
    e.FirstName,
	e.LastName,
	e.Salary,
	e.DOB,
	s.ID,
	s.DepartmentName,
	s.Profit,
	s.Revenues,
	s.ID_Employee
FROM dbo.tblEmployees e
RIGHT JOIN dbo.tblSales s
ON e.ID = s.ID_Employee;

Result: (Right Join)

Full Join

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

SELECT

    column_name(s)

FROM table1

FULL JOIN table2

ON table1.column_name = table2.column_name;

Query: (Full Join)

SELECT
    e.ID,
    e.FirstName,
	e.LastName,
	e.Salary,
	e.DOB,
	s.ID,
	s.DepartmentName,
	s.Profit,
	s.Revenues,
	s.ID_Employee
FROM dbo.tblEmployees e
FULL JOIN dbo.tblSales s
ON e.ID = s.ID_Employee;

Result: (Full Join)

ABOUT THE AUTHOR

Sabari M
Sabari M
Software Professional, India

IT professional with 14+ years of experience in Microsoft Technologies with a strong base in Microsoft .NET (C#.Net, ASP.Net MVC, ASP.NET WEB API, Webservices,...Read More

https://www.techieclues.com/profile/alagu-mano-sabari-m

Comments (0)

There are no comments. Be the first to comment!!!
×