Full Outer Join in SQL Server with an example



Inner Join and left join are the most commonly used joins in real time projects. It is very important that you understand the basics of joins before reading this article. Please read the articles below if you have not done so already.
1. Basics of Joins in SQL Server

2. Inner Join
3. Left Join
4. Right Join
Now, let us understand Full Outer join with an example.


Create 2 tables Company and Candidate. Use the script below to create these tables and populate them. CompanyId column in Candidate Table is a foreign key referencing CompanyId in Company Table.

CREATE TABLE Company
(
    CompanyId TinyInt Identity Primary Key,
    CompanyName Nvarchar(50) NULL
)
GO

INSERT Company VALUES('DELL')
INSERT Company VALUES('HP')
INSERT Company VALUES('IBM')
INSERT Company VALUES('Microsoft')
GO

CREATE TABLE Candidate
(
    CandidateId tinyint identity primary key,
    FullName nvarchar(50) NULL,
    CompanyId tinyint REFERENCES Company(CompanyId)
)
GO

INSERT Candidate VALUES('Ron',1)
INSERT Candidate VALUES('Pete',2)
INSERT Candidate VALUES('Steve',3)
INSERT Candidate VALUES('Steve',NULL)
INSERT Candidate VALUES('Ravi',1)
INSERT Candidate VALUES('Raj',3)
INSERT Candidate VALUES('Kiran',NULL)
GO



If you want to select all the rows from the LEFT Table ( In our example Candidate Table ) plus all the rows from the RIGHT table ( In our example Company Table ) , then we use FULL OUTER JOIN. A query involving a FULL OUTER JOIN for the Candidate and Company Table is shown below.

SELECT Cand.CandidateId, Cand.FullName, Cand.CompanyId, Comp.CompanyId, Comp.CompanyName
FROM Candidate Cand
FULL OUTER JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId

If we run the above query the output will be as shown in below. If you look at the out put, we now got 8 rows. All the rows from the Candidate Table and all the rows from the Company Table.


Full Outer Join Result

Instead of using FULL OUTER JOIN keyword we can just use FULL JOIN keyword as shown below. FULL OUTER JOIN or FULL JOIN means the same.

SELECT Cand.CandidateId, Cand.FullName, Cand.CompanyId, Comp.CompanyId, Comp.CompanyName
FROM Candidate Cand
FULL JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId

If you can think of any other sql server interview questions please post them as comments, so they will be useful to other users like you. This will be a great help from your side to improve this site.

6 comments:

  1. What is the difference between join & Union?

    ReplyDelete
    Replies
    1. sailaja bhamidipatyMarch 27, 2015 at 1:12 PM

      The difference between join & union are:
      Join the tables based on condition like inner or outer joins. Here columns are not specific order.
      For Union combining two or more select statements into one result set but it has to follow three rules like
      a.order of columns should be same
      b. columns has same data type
      c. Number of columns should be same.

      Delete
  2. Wha is the difference between right join and full outer join ..? here both are giving same values ... bit confusing

    ReplyDelete
    Replies
    1. Right join is the matching rows in both the Left and right tables plus non matching rows in the Right table.
      Full join is the matching rows in both Left and Right tables plus Non-matching rows in both Left and right tables.

      Delete
  3. Union is joining rows of two or more tables regarding to the same data type,order and number.but join does column s of two or more tables regarding to logical relationships like primary key and foreign key.

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us