Let’s start with a quick explanation of a join. Joins are used to combine
the data from two tables, with the result being a new, temporary table.
The temporary table is created based on column(s) that the two tables share,
which represent meaningful column(s) of comparison. The goal is to extract
meaningful data from the resulting temporary table. Joins are performed based
on something called a predicate, which specifies the condition to use in order
to perform a join.
It is best to illustrate the differences between full joins and inner joins
by use of an example. Here we have 2 tables that we will use for our example:
Employee_EmpID Location_EmpName
13 Sujoy
8 Sumit
3 Ram
17 Babu
25 Johnson
EmpID EmpLoc
13 Kolkata, India
8 Delhi,India
3 Pune, India
17 Chennai, India
39 Bangalore, India
For the purpose of our example, it is important to note that the very last
employee in the Employee table (Johson, who has an ID of 25) is not in the
Location table. Also, no one from the Employee table is from Bangalore
(the employee with ID 39 is not in the Employee table). These facts will be
significant in the discussion that follows.
Full joins
Let’s start the explanation with full joins. Here is what the SQL for a full
join would look like, using the tables above:
select * from employee full join location
on employee.empID = location.empID;
Subscribe to our newsletter on the left to receive more free interview questions!
A full join will return all rows that match based on the “employee.empID = location.empID”
join predicate, and it will even return all the rows that do not match – which is why
it is called a full join. The SQL above will give us the result set shown below:
Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Sujoy 13 Kolkata,India
8 Sumit 8 Delhi,India
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
25 Johnson NULL NULL
NULL NULL 39 Bangalore, India
You can see in the table above that the full outer join returned all the rows from
both the tables – and if the tables do have a match on the empID, then that is made
clear in the results. Anywhere there was not a match on the empID, there is a “NULL”
for the column value. So, that is what a full join will look like.
A full join is also known as a full outer join
It’s good to remember that a full join is also known as a full outer join – because
it combines the features of both a left outer join and a right outer join .
What about inner joins?
Now that we’ve gone over full joins, we can contrast those with the inner join. The
difference between an inner join and a full join is that an inner join will return
only the rows that actually match based on the join predicate – which in this case
is “employee.empID = location.empID”. Once again, this is best illustrated via an
example. Here’s what the SQL for an inner join will look like:
select * from employee inner join location on
employee.empID = location.empID
This can also be written as:
select * from employee, location
where employee.empID = location.empID
Now, here is what the result of running that SQL would look like:
Employee.EmpID Employee.EmpName Location.EmpID Location.EmpLoc
13 Sujoy 13 Kolkata,India
8 Sumit 8 Delhi,India
3 Ram 3 Pune, India
17 Babu 17 Chennai, India
The difference between the full join and inner join
We can see that an inner join will only return rows in which there is a match based
on the join predicate. In this case, what that means is anytime the Employee and
Location table share an Employee ID, a row will be generated in the results to show
the match. Looking at the original tables, one can see that those Employee ID’s that
are shared by those tables are displayed in the results. But, with a full join, the
result set will retain all of the rows from both of the tables.
No comments:
Post a Comment