In SQL, what’s the difference between a full join and an inner join?

 

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:

Employee Location
EmpID EmpName
13 Jason
8 Alex
3 Ram
17 Babu
25 Johnson
EmpID EmpLoc
13 San Jose
8 Los Angeles
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 Jason 13 San Jose
8 Alex 8 Los Angeles
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 Jason 13 San Jose
8 Alex 8 Los Angeles
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.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.