SQLancer
Practice
←
LESSON 7

SQL (Core) - OUTER JOINs

Progress: 0%

OUTER JOINs

While an INNER JOIN only retains rows that match in both tables, an OUTER JOIN preserves unmatched rows from one or both tables, populating missing values with NULL.

1. LEFT JOIN (LEFT OUTER JOIN) Syntax

SELECT building_name, role, name FROM buildings LEFT JOIN employees ON buildings.building_name = employees.building;

LEFT JOIN returns all records from the left table (buildings), regardless of whether a matching record exists in the right table (employees).

2. RIGHT JOIN & FULL OUTER JOIN

  • RIGHT JOIN: Retains all rows from the right table, pairing with left rows when matching.
  • FULL OUTER JOIN: Retains all rows from both tables, filling missing fields with NULL.
💡

Unmatched Data Pattern

To find empty buildings with no assigned employees, use LEFT JOIN combined with WHERE employees.building IS NULL.

🗄️ Schema Explorer SELECTABLE

› buildings
|

🎯 Exercises Checklist:

☐ 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)
⚡ Practice Console

Data Output

No results. Run query to fetch data.

Current Task Target

5 rows