Understanding Full Join in SQL Server with simple examples.

It returns all records from both tables, when there is a match in either left side table or right side table.

Full Join Diagram

FULL JOIN SYNTAX:

SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;

FULL JOIN EXAMPLE 1:

Requirement: I need to display all records from Country and also from Users

SELECT u.UserName,u.CountryName from users u FULL JOIN country c on u.countryid=c.countryid;

FULL JOIN EXAMPLE 2:

Requirement: I need to display all records from Orders and Customers

SELECT c.CustomerName,ord.OrderDate from Customers c FULL JOIN Orders ord ON c.Customerid=ord.Customerid

FULL JOIN EXAMPLE 3:

Requirement: I need to display all records from Course and Students

SELECT s.StudentName,c.CourseName from Students s FULL JOIN Course c ON s.Studentid = c.Studentid