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)
Comments (0)