Open In App

Full join and Inner join in MS SQL Server

Last Updated : 22 Jul, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

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

Name Rollno Age Course
Ayra 111 19 CSE
Mona 112 18 EEE
Veena 113 19 ECE
Neena 114 18 Mech

Table: Mark

Name Rollno Course GPA
Ayra 111 CSE 9.6
Mona 112 EEE 9.5
Veena 113 ECE 7.7
Neena 114 Mech 7.5

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;
Name Rollno Age Course GPA
Ayra 111 19 CSE 9.6
Mona 112 18 EEE 9.5
Veena 113 19 ECE 7.7
Neena 114 18 Mech 7.5

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;
Name Rollno Course
Ayra 111 CSE
Mona 112 EEE
Veena 113 ECE
Neena 114 Mech


Next Article
Article Tags :

Similar Reads