Join (SQL) facts for kids

A SQL Join is like a special tool in a database that helps you combine information from two or more tables. Imagine you have different lists of information, and you want to see how they connect. A Join command helps you do just that! Database programmers use Joins to bring related data together. There are five main types of Joins: `JOIN` (also called `INNER JOIN`), `LEFT OUTER JOIN`, `RIGHT OUTER JOIN`, `FULL OUTER JOIN`, and `CROSS JOIN`. For a Join to work, the tables must have some matching information.

Inner Join (The Basic Join)

Let's imagine you have two tables: one for Employees and one for Departments.

Suppose you want to know which employee works in which department. You could look at the ID numbers in both tables. But a much easier way to see all this information together is by using an `INNER JOIN`. These tables can be joined because they both share the DepartmentID.

Here's the command you would use:

SELECT LastName, DepartmentName FROM employee join department on department.DepartmentID = employee.DepartmentID;

This command would create a new table that looks like this:

Notice that Williams is not in the final table. This is because Williams did not have a DepartmentID in the Employee table. An `INNER JOIN` only shows rows where there's a match in both tables.

Outer Joins

What if you want to see all the information from one table, even if there isn't a match in the other table? That's when you use an `OUTER JOIN`. Inner joins only show matching records. Outer joins are different because they can include rows that don't have a match.

Left Outer Join (Left Join)

A `LEFT OUTER JOIN` (or just `LEFT JOIN`) will show all the records from the table on the left side of your command. It will also show any matching records from the table on the right. If there's no match on the right side, it will just show "NULL" (meaning "nothing") for those spots.

Let's use our Employee and Department tables again:

Here's the command to do a `LEFT OUTER JOIN`:

SELECT FROM employee 
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

This is what the result table would look like:

See how Williams is included this time? That's because Employee was the "left" table in our command. Even though Williams didn't have a department ID, the `LEFT JOIN` still returned all the employee information. The "Department" columns just show "NULL" because there was no match.

Right Outer Join (Right Join)

A `RIGHT OUTER JOIN` (or `RIGHT JOIN`) works just like a `LEFT JOIN`, but it focuses on the "right" table. It will return all the records from the table on the right side of your command. It will also show any matching records from the table on the left. If there's no match on the left side, it will show "NULL" for those spots.

Using the same tables:

Here's the command for a `RIGHT OUTER JOIN`:

SELECT FROM employee RIGHT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

And this is the final result:

In this case, Marketing is included even though no employee has that DepartmentID. This is because Department was the "right" table, so all its records are shown. The "Employee" columns show "NULL" for Marketing because there was no matching employee.

How Joins are Flexible

Left and right outer joins are very similar. The main difference is which table you consider the "main" one. You can often get the same result by switching the order of the tables and changing the join type.

For example, if you switch the tables in the `LEFT OUTER JOIN` command:

Instead of this:

SELECT FROM employee 
LEFT OUTER JOIN '''department''' ON employee.DepartmentID = department.DepartmentID;

You could write this:

SELECT FROM department 
LEFT OUTER JOIN '''employee''' ON employee.DepartmentID = department.DepartmentID;

This would give you the same result as the `RIGHT OUTER JOIN` example we just saw! It's because you've changed which table is considered the "left" one.

See also

In Spanish: Sentencia JOIN en SQL para niños