A brief explanation of a join
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:
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;
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:
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:
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.