Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL Server - Get Started
  • Install SQL Server
  • SQL Server Management Studio
  • SQL Server - Windows Authentication
  • SQL Server - Authentication
  • SQL Server - Create New User
  • SQL Server - GRANT/REVOKE Permissions to User
  • SQL Server - Data Types
  • SQL Server - Naming Conventions
  • SQL Server - CREATE Database
  • SQL Server - CREATE Table
  • Add Columns
  • Identity Column
  • Rename Column, Table
  • Drop Columns
  • SQL Server - Schema
  • SQL Server - Tables Relations
  • SQL Server - Primary Keys
  • Modify/Delete Primary Keys
  • SQL Server - Foreign Keys
  • Modify/Delete Foreign Keys
  • SQL Server - Check Constraints
  • SQL Server - Unique Constraints
  • SQL Server - Views
  • Modify/Delete Views
  • SQL Server - Functions
  • SQL Server - Stored Procedures
  • Stored Procedure Parameters
  • SQL Server - Indexes
  • Non-clustered Indexes
  • Modify/Delete Indexes
  • SQL Server - Triggers
  • DDL Triggers
  • LOGON Triggers
  • Enable/Disable Triggers
  • Modify/Delete Triggers
  • SQL Server - Sequence
  • SQL Server - Synonyms
  • SQL Server - IF ELSE Statement
  • SQL Server - Loops
  • SQL Server - Insert Data
  • SQL Server - Update Data
  • SQL Server - Delete Data
  • SQL Server - Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL Server - Inner Join
  • Left Join
  • Right Join
  • Full Join
  • Self Join
  • Dynamic SQL
  • Built-in Functions
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

SQL Server - FULL JOIN Query

The FULL JOIN returns all the records all the specified tables. It includes NULL for any non-matching records.

In some databases, FULL JOIN is called FULL OUTER JOIN. It can return a very large result set because it returns all the rows from all the tables.

Syntax:

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

For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table
EmpIdFirstNameLastNameEmailSalaryDeptId
1'John''King''[email protected]'330001
2'James''Bond'
3'Neena''Kochhar''[email protected]'170002
4'Lex''De Haan''[email protected]'150001
5'Amit''Patel'180004
6'Abdul''Kalam''[email protected]'250002
Department Table
DeptIdName
1'Finance'
2'HR'
3'Sales'

Now, look at the following FULL JOIN query.

SQL Server: RIGHT JOIN Query
SELECT emp.EmpId, emp.FirstName, dept.DeptId, dept.Name FROM Employee emp FULL JOIN Department dept ON emp.DeptId = dept.DeptId;

The above query will display the following result.

DeptIdNameEmpIdFirstName
1'John'1'Finance'
2'James'NULLNULL
3'Neena'2'HR'
4'Lex'1'Finance'
5'Amit'NULLNULL
6'Abdul'2'HR'
NULLNULL3'Sales'

As you can see, it fetches all the records from both tables. First, it fetches all the records from the left table Employee and then all the records from the right table Department. It includes NULL for all non-matching records from both tables.

Now, let's swap the tables and see how the result will be changed.

SQL Server: RIGHT Join Query
SELECT emp.EmpId, emp.FirstName, dept.DeptId, dept.Name FROM Department dept FULL JOIN Employee emp ON emp.DeptId = dept.DeptId;

The above query will display the following result.

EmpIdFirstNameDeptIdName
1'John'1'Finance'
4'Lex'1'Finance'
3'Neena'2'HR'
6'Abdul'2'HR'
NULLNULL3'Sales'
2'James'NULLNULL
5'Amit'NULLNULL

As you can see, first it displays all the records from the right table Department and then records from the left table Employee. It returns NULL for all non-matching records in both tables.

TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.