A Join clause is used to combine rows from two or more tables, based on a related column between them.
Different types of Joins are:
- Inner Join
- Right Join
- Left Join
- Full Join
For a better understanding of different table joins. We will look at data in two different tables.
In the student table, there are three columns in the student table. Id, Name and Gender. In which Name and Gender are varchar columns and Id is numeric columns. There is a total of 7 rows in the student table.
In the student Details table, there are three columns in the student table. Id, City and Hobbies. In which City and Hobbies are varchar columns and Id is numeric columns. There is a total of 7 rows in the student table.
Let’s see each join
Inner Join
Returns that records which are same in both the students and student Details tables. Let’s run SQL query for Inner Join.
- Syntax:
select * from students A
inner join students_Details B
where A.id = B.id;
Left Join
Left Join in SQL returns all the values from the Left table, it will also show all the matching records from the right table and if there are no matching join it will return Null.
- Syntax:
select * from students A
Left join students_Details B
on A.id = B.id
Right Join
Right Join in SQL returns all the values from the Right table, it will also show all the matching records from the left table and if there are no matching join it will return Null.
- Syntax:
select * from students A
Right join students_Details B
on A.id = B.id
Full Outer Join
The SQL full join is the result of a combination of both left and right outer join and the join tables have all the records from both tables.
It puts NULL on the place of matches not found. SQL full outer join and SQL join are the same. Generally, it is known as SQL FULL JOIN.
- Syntax:
select * from students A
Right join students_Details B
on A.id = B.id
union
select * from students A
Left join students_Details B
on A.id = B.id;
