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.
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 operators return rows even if they do not have related rows in the final table. We’ll discuss the three types of
OUTER JOINs, how they work, and how you can use them in your
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
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
SELECT name FROM employees;
Our query returns the following:
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:
OUTER JOIN, and
INNER JOINs return rows that have a match in both the tables you are querying, whereas
OUTER JOINs return rows even if they don’t have related rows in the joined table.
CROSS JOINs 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
LEFT OUTER JOINSreturn unmatched rows from the left table;
RIGHT OUTER JOINSreturn unmatched rows from the right table;
FULL OUTER JOINSreturn unmatched rows from all tables.
OUTER JOINs 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:
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
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:
FREE Python Fundamentals Workshop
Use the calendar below to reserve your seat.
Demand for people who know Python is soaring! In this free online workshop, learn the fundamentals of Python and meet other Career Karma members who are building with Python.
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
If you want to learn more about left joins, you can read the Career Karma tutorial on the
SQL operation here.
SQL Joins: Right
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:
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
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.
Full SQL Outer Joins
Full OUTER JOINs are an uncommon type of
SQL join that can be used to return unmatched records from both tables. Often,
full OUTER JOINs 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:
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.
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
In this tutorial, we focused on
OUTER JOINs, 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
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!