When do i use an outer join

Check out the beginning. When performing an inner join , rows from either table that are unmatched in the other table are not returned. In an outer join, unmatched rows in one or both tables can be returned. There are a few types of outer joins:.

As you work through the following lessons about outer joins, it might be helpful to refer to this JOIN visualization by Patrik Spathon. The data for the following lessons was pulled from Crunchbase , a crowdsourced index of startups, founders, investors, and the activities of all three. It was collected Feb. The first table lists a large portion of companies in the database; one row per company. Execute this command and view the output.

It only returns rows that do not match either in Employee or Departments table. These EmpID does not exist in the right side Department table. We need to note the table placement position in the Join statement. Currently, we have an Employee table on the left side and Departments table in Right side. In this query, we have the Department table in left position, so the Left Outer Join should check the values for this table and return a NULL value in case of a mismatch.

It is because EmpID 11 is not available in the Employee table. In the following image, you can see we get all matching rows along with one row with NULL values. Null value row has EmpID 11 because it does not exist in the Employee table. You can also notice the position of the Department table is in the right position in Join. Due to this, we do not get values from the Employee table left position which does not match with Department table Right side.

As highlighted earlier, the table position is important in the JOIN statement. If we change the table positions, we get different output. In the following query, we have Departments table Left and Employee table Right. You can notice the difference in Right Outer Join after swapping tables positions in above query. In the following output, we get all matching records, unmatch records from the left table and unmatch records from the right table.

I hope you found this article helpful. Feel free to provide feedback in the comments below. N 'Charlotte Robinson' ,. N 'Chicago' ,. N 'Consultant'. N 'Madison Phillips' ,. N 'Dallas' ,. N 'Senior Analyst'. N 'Emma Hernandez' ,. N 'Phoenix' ,. N 'Samantha Sanchez' ,. Sometimes nulls will be produced in this process as some data is shared while other data is not.

A left outer join will return all the data in Table 1 and all the shared data so, the inner part of the Venn diagram example , but only corresponding data from Table 2, which is the right join. In our example database, there are two products — oranges and tomatoes — on the 'left' Prices table that do not have a corresponding entry on the 'right' Quantities table. The other rows in the result are the same as the inner join. A right outer join returns Table 2's data and all the shared data, but only corresponding data from Table 1, which is the left join.

Similar to the left join example, the output of a right outer join includes all rows of the inner join and two rows — broccoli and squash — from the 'right' Quantities table that do not have matching entries on the left.

A full outer join, or full join, which is not supported by the popular MySQL database management system, combines and returns all data from two or more tables, regardless of whether there is shared information.

Think of a full join as simply duplicating all the specified information, but in one table, rather than multiple tables. Where matching data is missing, nulls will be produced. These are just the basics, but many things can be done with joins. There are even joins that can exclude other joins! This video explains the difference between various types of joins.