Full join and Inner join in MS SQL Server

Full Join

Full join selects all the rows from the left and right tables along with the matching rows as well. If there are no matching rows, it will be displayed as NULL.

Syntax:

select select_list 
from table1 full join table2 on join _predicate
                           (OR)
select table1.*, table2.* 
from table1 full join table2
on join _predicate
                           (OR)
select * 
from table1 full join table2
on join _predicate

Inner Join

Inner join retrieves the rows that match from the left and right tables. If there are no matching rows, NULL is displayed.

Syntax:

select select_list 
from table1 inner join table2 on join_predicate
                        (OR)
select table1.*, table2.* 
from table1 full join table2
on join _predicate
                           (OR)
select * 
from table1 inner join table2 
on join _predicate

Note:These joins can be applied to multiple tables.

SELECT *

FROM table1

INNER JOIN table2

ON join _predicate

In this query it is not necessary that there should be the identical columns in both table. Because SQL server will understand automatically and it will give us all the columns from both tables. It will only give the one column for the column on which we are giving ON condition.

Example of Full join and Inner join

There are two tables namely Student and Marks from the university database given below.

Table: Student

Table: Mark

Example 1: Full Join

Full join is applied to the tables Student and Marks and the table below is the result set.

SELECT student.Name, student.Rollno, student.Age, student.Course, marks.GPA
FROM student FULL JOIN marks ON student.Rollno = marks.Rollno;

Example 2: Inner join

Inner join is applied to the tables Student and Marks and the table below is the result set.

SELECT student.Name, student.Rollno, student.Course
FROM student INNER JOIN marks ON student.Rollno = marks.Rollno;