When you’re working with a database, you may want to query two or more tables at the same time and create a combined set of results. For example, you may want to find out the names of every employee and the department for which they work, where those two pieces of information are stored in two different tables.
That’s where SQL
joins come in. When you join tables, you can run a query across multiple tables and create a combined result.
In this guide, we are going to focus on one type of join: the OUTER JOIN
. OUTER JOIN
operators return rows even if they do not have related rows in the final table. We’ll discuss the three types of OUTER JOIN
s, how they work, and how you can use them in your SQL
queries.
SQL Joins Refresher
Queries, which usually begin with the SELECT
statement, are used to retrieve information from a database. Usually, when you are writing a query you will focus on retrieving information from one table and use the FROM
clause to specify what table your command will query. Here is the syntax for writing a standard SQL
query:
SELECT column_name FROM table_name WHERE certain_conditions_are_met;
Here’s an example of a query that will retrieve a list of the names of every employee in the employees
table:
SELECT name FROM employees;
Our query returns the following:
name |
Luke Mike Hannah Geoff Alexis Emma Jonah Adam |
(8 rows)
We could also use the WHERE
clause to filter out the results of our query to include only records that meet a certain set of criteria. For example, we could write a query that finds the names of all sales associates or all executive staff.
But what if we want to get information from multiple tables, and combine the result into one table? That’s where joins come in.
Joins allow you to query multiple tables and create a combined result set with the matched rows. There are three main types of joins: INNER JOIN
, OUTER JOIN
, and CROSS JOIN
.
INNER JOIN
s return rows that have a match in both the tables you are querying, whereas OUTER JOIN
s return rows even if they don’t have related rows in the joined table. CROSS JOIN
s are joins without a join condition, and each row of a table is combined with the corresponding row of another table.
SQL Outer Joins
As we have discussed, when you are performing an INNER JOIN
, rows from either table that are unmatched in another table will not be returned in the results set. But what if we wanted to get these values? We can use the OUTER JOIN
to retrieve this data.
There are three types of OUTER JOIN
:
LEFT OUTER JOINS
return unmatched rows from the left table;RIGHT OUTER JOINS
return unmatched rows from the right table;FULL OUTER JOINS
return unmatched rows from all tables.
OUTER JOIN
s are capable of matching records from both tables based on our needs and will return all records relevant to the type of join that we use.
SQL Joins: Left
Left joins return all rows from the left table and rows from the right table where the join condition is met.
Let’s say that we have two tables: employees
and company_departments
. Now suppose that we wanted to get the name of every employee as well as their department names, even if they are not assigned to any department. We could get this information by using a LEFT JOIN
.
Below is an example of a LEFT JOIN
query that will retrieve the name of every employee and their department name by combining the employees and company_departments tables together using their common value: department_id.
SELECT employees.name, company_departments.name FROM employees LEFT JOIN company_departments ON employees.department_id = company_departments.department_id;
Our query returns the following:
name | name |
Luke | Sales |
Mike | Sales |
Hannah | Sales |
Geoff | Sales |
Alexis | Sales |
Emma | Marketing |
Jonah | Executive |
Adam |
(8 rows)
As you can see, the LEFT JOIN
has returned all rows from the employees
table, even if the department for which they work cannot be found in the company_departments
table.
If you want to learn more about left joins, you can read the Career Karma tutorial on the SQL
operation here.
SQL Joins: Right
The RIGHT JOIN
statement is the direct opposite of the LEFT JOIN
operator. RIGHT JOIN
returns all rows from the right table as well as rows from the left table where the join condition is met.
Let’s say that we want to get a list of the names of all departments as well as the names of every employee who works in those departments. And we also want to get data for departments where no employees are assigned. We could use the following query to get this data:
SELECT employees.name, company_departments.name AS "DeptName" FROM employees RIGHT JOIN company_departments ON employees.department_id = company_departments.department_id;
The above query returns the following:
name | “deptname” |
Luke | Sales |
Mike | Sales |
Hannah | Sales |
Geoff | Sales |
Alexis | Sales |
Emma | Marketing |
Jonah | Executive |
Payroll | |
Information Technology |
(9 rows)
As you can see, our query has returned a list of all departments, as well as the names of every employee who works for those departments. The query has also returned the departments Payroll
and Information Technology
, which currently have no staff.
In addition, our query did not return Adam, who was the employee from our LEFT JOIN
query who was not assigned to any department. This is because the RIGHT JOIN
includes all rows from the right table—company_departments—where our conditions are met, and Adam does not have any affiliation to any record in the right table.
"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"
Venus, Software Engineer at Rockbot
Full SQL Outer Joins
Full OUTER JOIN
s are an uncommon type of SQL join
that can be used to return unmatched records from both tables. Often, full OUTER JOIN
s are used with aggregate functions to understand how much overlap exists between two tables.
Let’s say that we want to get a list of all employees and departments. We could use the following SQL
query to retrieve that information:
SELECT employees.name, company_departments.name AS "DeptName" FROM employees FULL OUTER JOIN company_departments ON employees.department_id = company_departments.department_id;
Our query returns the following:
name | “deptname” |
Luke | Sales |
Mike | Sales |
Hannah | Sales |
Geoff | Sales |
Alexis | Sales |
Emma | Marketing |
Jonah | Executive |
Adam | |
Payroll | |
Information Technology |
(10 rows)
The above result set includes a list of every employee’s name, as well as the department they are assigned to, even if they are not assigned to a department. The result set also includes a list of all department names, even if a department has no employees assigned to it.
Conclusion
Joins
are an SQL
server function that allow you to query two or more tables at the same time and create a combined result set. For example, if you wanted to get a list of company employees, as well as the date their most recent payroll check was processed, you could use a join
.
In this tutorial, we focused on OUTER JOIN
s, which return the rows in a join
even if they don’t have related rows in the joined table. We also discussed the three main types of OUTER JOIN
s—LEFT JOIN
, RIGHT JOIN
, and full OUTER JOIN
—and explored how they work in practice.
Now you’re ready to perform SQL JOIN
operations like an expert!
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.