Depending on how you want to analyze the data, the INNER JOIN we used last lesson might not be sufficient because the resulting table only contains data that belongs in both of the tables.

If the two tables have asymmetric data, which can easily happen when data is entered in different stages, then we would have to use a LEFT JOIN, RIGHT JOIN or FULL JOIN instead to ensure that the data you need is not left out of the results.

Select query with LEFT/RIGHT/FULL JOINs on multiple tables

SELECT column, another_column, … FROM mytable **INNER/LEFT/RIGHT/FULL JOIN another_table ON mytable.id = another_table.matching_id** WHERE _condition(s)_ ORDER BY column, … ASC/DESC LIMIT num_limit OFFSET num_offset;

Like the INNER JOIN these three new joins have to specify which column to join the data on.
When joining table A to table B, a LEFT JOIN simply includes rows from A regardless of whether a matching row is found in B. The RIGHT JOIN is the same, but reversed, keeping rows in B regardless of whether a match is found in A. Finally, a FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.

When using any of these new joins, you will likely have to write additional logic to deal with NULLs in the result and constraints (more on this in the next lesson).

Did you know?

You might see queries with these joins written as LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN, but the OUTER keyword is really kept for SQL-92 compatibility and these queries are simply equivalent to LEFT JOIN, RIGHT JOIN, and FULL JOIN respectively.

Exercise

In this exercise, you are going to be working with a new table which stores fictional data about Employees in the film studio and their assigned office Buildings. Some of the buildings are new, so they don't have any employees in them yet, but we need to find some information about them regardless.

Since our browser SQL database is somewhat limited, only the LEFT JOIN is supported in the exercise below.

1. Find the list of all buildings that have employees
	1. SELECT Distinct Building FROM Employees E INNER JOIN Buildings B;
	2. SELECT Distinct Building FROM Employees E LEFT JOIN Buildings B;
2. Find the list of all buildings and their capacity
	1. SELECT * FROM Buildings;
	2. SELECT Building_name, Capacity FROM Buildings B INNER JOIN Employees E ON B.Building_name = E.Building;
	3. SELECT Building_name, SUM(Capacity) FROM Buildings B INNER JOIN Employees E ON B.Building_name = E.Building GROUP BY Building_name;
3. List all buildings and the distinct employee roles in each building (including empty buildings)
	1. SELECT Building_name, Role FROM Buildings B LEFT JOIN Employees E ON B.Building_name = E.Building GROUP BY Building_name, Role;
	2. SELECT DISTINCT B.Building_name, E.Role FROM Buildings B LEFT JOIN Employees E ON B.Building_name = E.Building;
	3. SELECT B.Building_name, COALESCE(E.Role, 'No Employee') FROM Buildings B LEFT JOIN Employees E ON B.Building_name = E.Building GROUP BY B.Building_name, E.Role;
	   

Exercise 7 — Tasks

1. Find the list of all buildings that have employees ✓
2. Find the list of all buildings and their capacity ✓
3. List all buildings and the distinct employee roles in each building (including empty buildings) ✓

This webpage is an interactive SQL tutorial lesson from SQLBolt, focusing on OUTER JOINs in SQL. It explains the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, highlighting that OUTER is mainly for compatibility and can usually be omitted. The lesson offers examples and syntax for using these joins, with emphasis on LEFT JOIN (the only supported join in this online exercise).

You are given two tables:

• Buildings: Contains building names and capacities.
• Employees: Lists employees, their roles, building assignment, and years employed.

The exercise asks you to:

• Find all buildings that have employees.
• Find all buildings and their capacity.
• List all buildings and the distinct employee roles in each (including empty buildings).

These tasks are designed to help you practice querying with OUTER JOINs, especially for scenarios with incomplete or asymmetric data between tables, and require writing SQL queries that may deal with NULLs in the results.

1

Here are several other ways you can write the same query, achieving similar results:

1. Use Table Aliases Differently

SELECT B.Building_name, E.Role
FROM Buildings AS B
LEFT JOIN Employees AS E ON B.Building_name = E.Building
GROUP BY B.Building_name, E.Role;

2. Without Aliases

SELECT Buildings.Building_name, Employees.Role
FROM Buildings
LEFT JOIN Employees ON Buildings.Building_name = Employees.Building
GROUP BY Buildings.Building_name, Employees.Role;

3. Using DISTINCT (if you do not need GROUP BY)

SELECT DISTINCT B.Building_name, E.Role
FROM Buildings B
LEFT JOIN Employees E ON B.Building_name = E.Building;

• This works if you only want unique building-role pairs and do not need aggregate functions.

4. With COALESCE to Handle NULL Roles (optional, for display)

SELECT B.Building_name, COALESCE(E.Role, 'No Employee')
FROM Buildings B
LEFT JOIN Employees E ON B.Building_name = E.Building
GROUP BY B.Building_name, E.Role;

• This displays 'No Employee' instead of NULL in places where there are no employee roles for a building.

All these queries perform the LEFT JOIN to include all buildings, showing the roles present (or no role if none), and group or select unique results as required. The result is effectively the same as your original query, but with variations in style and clarity.

1