FULL OUTER JOIN | FULL JOIN | OUTER JOIN | ANSI JOIN | SQL JOIN #techpointfundamentals

FULL OUTER JOIN | FULL JOIN | OUTER JOIN | ANSI JOIN | SQL JOIN | #techpointfundamentals

The OUTER JOIN is an extension of INNER JOIN or EQUI JOIN. By using the OUTER JOIN we can retrieve both matching and non-matching data from the tables. OUTER JOIN is classified into three categories:

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN

In this video, we will see the FULL OUTER JOIN with a real example.

FULL OUTER JOIN is the combination of both LEFT OUTER JOIN and RIGHT OUTER JOIN.

So FULL OUTER JOIN returns both matching and non-matching data from both the tables. The non-matching data of the tables take the default NULL value in this case.

SELECT
U.Name,
U.Email,
U.MobileNumber,
R.Name AS UserRole

FROM [User] U
FULL OUTER JOIN UserRole UR ON U.Id = UR.UserId
FULL OUTER JOIN [Role] R ON UR.RoleId = R.Id
Order By R.Name

Now we prove that: FULL OUTER JOIN = LEFT OUTER JOIN + RIGHT OUTER JOIN

The below query will return the same result as the above FULL JOIN query:

-- LEFT JOIN
SELECT
U.Name,
U.Email,
U.MobileNumber,
R.Name AS UserRole

FROM [User] U
LEFT OUTER JOIN UserRole UR ON U.Id = UR.UserId
LEFT OUTER JOIN [Role] R ON UR.RoleId = R.Id

UNION

-- RIGHT JOIN
SELECT
U.Name,
U.Email,
U.MobileNumber,
R.Name AS UserRole

FROM [User] U
RIGHT OUTER JOIN UserRole UR ON U.Id = UR.UserId
RIGHT OUTER JOIN [Role] R ON UR.RoleId = R.Id
Order By R.Name

Subscribe to our YouTube Channel: https://www.youtube.com/channel/UCgl6eGIDCu69Vg1UnxKj9Xw?sub_confirmation=1
Visit our Website for more Articles: https://www.techpointfunda.com

Recommended Videos Playlists:
============================================================
SQL Videos Playlist: https://www.youtube.com/embed/videoseries?list=PL99CiVAQoSUTgmk8f8FmdkC0DJpdkAXKJ
C# Interview Questions and Answers Videos Playlist: https://www.youtube.com/embed/videoseries?list=PL99CiVAQoSUQ7iYn7p7qumuYqzIAHJY6q
Visual Studio Tips and Tricks Videos Playlist: https://www.youtube.com/embed/videoseries?list=PL99CiVAQoSUSxTFfo50vqhMw6ee9CMaCs
Interview Questions and Answers Videos Playlist: https://www.youtube.com/embed/videoseries?list=PL99CiVAQoSUQGLXhfDFtnII91w_oLVjLB
C# Videos Playlist: https://www.youtube.com/embed/videoseries?list=PL99CiVAQoSUS_AbYw2Woe4IuLqJ1ecvW0
C# 8 Videos Playlist: https://www.youtube.com/embed/videoseries?list=PL99CiVAQoSURZR-oiFulBjQMGtjLFGuc1


Recommended SQL Videos:
============================================================
RIGHT OUTER JOIN video: https://youtu.be/i8-lY3xsHRo
INNER JOIN vs LEFT JOIN with WHERE statement video: https://youtu.be/dhlJGvkxtiA
LEFT OUTER JOIN or LEFT JOIN video: https://youtu.be/lZ9jpDnValw
SELF JOIN video: https://youtu.be/o-Ld5-IFLZg
EQUI JOIN and NON-EQUI JOIN video: https://youtu.be/IL4GIxIHDzo
ANSI JOIN and NON-ANSI JOIN video: https://youtu.be/QrPjNGRmRXM
INNER JOIN or DEFAULT JOIN video: https://youtu.be/Mv7dYRRmcUU


Recommended C# Interview Questions Videos:
============================================================
Sealed Method Interview Questions and Answers videos: https://youtu.be/DBLbUWzRZBI
Sealed Class Interview QA Video: https://youtu.be/nJhNk9rvk6c
Static Class Interview QA Video: https://youtu.be/oYHHs7nb-X8
Abstract Class vs Interface Video: https://youtu.be/hVlTLTRSRfo

Recommended C# Videos:
============================================================
Shadowing or Method Hiding video: https://youtu.be/1WeZTrqW0AA
Virtual Methods and Method Overriding video: https://youtu.be/A9-MBBbs5Ok
Abstract Class and Methods video: https://youtu.be/UWZFQASkgDE
Sealed Class Video: https://youtu.be/8ruDAINskGs
Static Class Video: https://youtu.be/POfQ9IrcEYA
Sealed Interface Method Video: https://youtu.be/OsJZSOO3R7Q
Virtual Interface Method Video: https://youtu.be/Lhu9b-TYpfo
Partial Classes Video: https://youtu.be/bZUoqRNKoQY
Partial Methods Videos: https://youtu.be/hesNzrFqWcQ


Follow Us on Social Media Platforms:
============================================================
Follow us on Facebook: https://www.facebook.com/TechPointFundamentalsOfficials
Visit our Facebook Page: https://www.facebook.com/TechPointFundamentals
Join our Facebook Interview Questions and Answers Group: https://www.facebook.com/groups/InterviewPreparationByTechPoint
Join our Facebook IT Group: https://www.facebook.com/groups/iTechFunda

Follow us on LinkedIn: https://www.linkedin.com/in/TechPointFundamentals
Join our LinkedIn Interview Questions and Answers Group: https://www.linkedin.com/groups/13906177

Follow us on Telegram: https://t.me/TechPointFundamantals
Join our Telegram Interview Questions and Answers Group: https://t.me/InterviewQuestionsAndAnswers

Follow us on VK: https://vk.com/techpointfundamentals

Follow us on Twitter: https://twitter.com/TechPointFunda
Follow us on Mix: https://mix.com/techpointfundmentals
Follow us on Tumblr: https://techpointfundamentals.tumblr.com