Joins are one of the most common operators in SQL queries. In this post, we present inner and outer joins and explain the diferences between them. We use SQL ANSI syntax which runs in most of the widelly used DBMS, like Oracle, MS SQL Server, PostgreSQL and MySQL.
Departments and Employees
Let’s first presentes the Departments and Employees tables, represented in the following figure. The Employees table stores each employee’s identifier and name and the identifier of the department to which the employee belongs. The Departments table stores the identifier and name of each department and the id of the department’s manager.
Inner Joins
We use the Inner Join to retrieve rows that have the matching values in both tables. In the following query, an inner join is used to list the id and name of each department and the name of its manager.
The Sales department does not have a manager. Then, the ManagerId column for such row does not have a matching value in the employees table. So, the Sales department is not included in the query’s resultset. If one wants to include all the departmments in the resultset, then he/she should use an outer join, as we explain later in this post.
SQL joins can be used in queries that have more than two tables. In the following, we present how to list the names of all the managers, the name of the departments they manage and the name of the department they belongs to. Note that the query references the departments table twice .
Left Outer and Right Outer Joins
Using Outer joins, we can join tables and include some rows that do not having matching values in both tables. The unique difference between Left Outer Joins and Right Outer Joins is the position in the query of the table whose rows should be included in the resultset even though they do not have corresponding values in the other table.
Suppose we want to list ALL departments and the name of their managers, if they have one. This is different to which we did before, when we listed only the departments that have a manager. To list ALL the departmets and the name of their managers, if they have one, we use an Outer Join. This makes the DBMS include in the query the departments that do not have a manager (i.e. whose value in the Managerid column from the Departments table does not having a matching value at the column Empid from Employees table).
In the following, we use a Left outer join to list ALL departments (including the ones that do not have managers) and the name of their managers (if they have one). Note that the Departments table is at the left of the join clause.
We can have the same result using a Right outer join. To do that, we just have to change the tables positions in the query:
It is also possible to use left/right outer joins instead of Not Exists and Not In to query for rows that do not have matching values (which can avoid having problems like the ones with Not In we present here). To find such rows with no-matching values, just filter for NULL in the join column.
In the following we use an outer join to list the employees that aren’t managers.
Full Outer Joins
We can think of Full Outer Joins as a combinations of Left Outer and Right Outer joins. When we use a full outer join, the DBMS lists all the rows of both joining tables, no matter if there are matching values int the join column(s). But it places the rows with matching values together.
In the following, we use a Full outer join to list ALL the employees and ALL the departments. Each department id and name are next to its manager.
You can use full outer joins in queries with several tables (like we did with inner joins). But be careful when combining multiple outer joins so you don’t lose the effects of any of them.