In this article, we are going to discuss joins in SQL. joins is a very important topic in SQL. The basic use of joins is to return the result sets from multiple tables.
Different types of joins are there
1. Inner Join
2. Left Join
3. Right Join
4. Full Join
5. Self Join
6. Cartesian Join
In this article, we will take one example to understand the joins.
let’s look at the below Employee table
let’s look at the Address table.
In this example, we will combine the result set between two tables, Notice that the EmployeeID column is common in two tables, in the following article we can check how to apply joins
1. INNER JOIN
Inner join keyword we use to join more than two tables. Inner join take the only matching records from both tables, combined the result set, and returns the records if the condition is satisfied.
Note: Inner Join and Join both are the same, We can write the Join keyword for Inner join also.
This query will return the Employee Name and Address from both tables when the condition matches(Inner Join),
Select E.[Employee Name],A.Address from Employee E
Inner Join Address A on E.EmployeeID=A.EmployeeID
The result set of the above query is
2. LEFT JOIN:
A left join is used to get the matching records from the right table but ALL the records from the left table, If the condition is matched. Data in Non-matching rows in the left table will show the NULL values.
Note: There is no difference between LEFT JOIN and LEFT OUTER JOIN
This query will return the Employee Name and Address from both tables, Select all records from the left table but match from the right table.
Select E.[Employee Name],A.Address from Employee E
Left Join Address A on E.EmployeeID=A.EmployeeID
The above query will return the result set, For Non-matching records, it will return the NULL values. Take a look at the record at rows 3 and 5.
3. RIGHT JOIN
Right, join is used to get All the rows from the Right table but Only matching records from the left table. Data in non-matching rows in the right table will show the null values.
Note: RIGHT JOIN and RIGHT OUTER JOIN both are the same.
This query will return the Employee Name and Address from both tables, Select all records from the Right table but match from the Left table.
Select E.[Employee Name],A.Address from Employee E
Right Join Address A on E.EmployeeID=A.EmployeeID
This query will return a result set
Look at the result set, For Non-matching rows, we got Null values.
4. FULL JOIN
Full Join returns the combination of the result of Both left and Right join. For non-matching rows, we will get NULL values.
Note: Full Join and Full Outer Join both are the same.
This query will return all the records from both tables, Matching or non-matching (Full Join)
Select E.[Employee Name],A.Address from Employee E
full Join Address A on E.EmployeeID=A.EmployeeID
The result set of the above query is
This query returning 8 records, 3 Matching and 5 Non-matching- From Both tables
5. SELF JOIN
Self Join used to join the table to itself. In some conditions, we need to records from the same table based on condition. One table row is compared with itself and the other rows of the table
Take a look at the below query
select E.EmployeeID,E.[Employee Name] from Employee E, Employee E1
Where E.Salary>E1.Salary
Output:
6. CARTESIAN JOIN
Cartesian Join is used to return the Cartesian result sets. In another word, one row from one table is every single row of another table
Note: Cartesian Join and Cross Join both are the same
select [Employee Name],Address from Employee
cross join Address
In our example, Cross join will return a total of 30 records (5*6)