Beginner

538. One-to-One JOIN

Learn how JOINs work when each record in one table corresponds to at most one record in another table.

📘 Lesson 538 of 728 ⏱ 11 min 🎯 Intermediate

A one-to-one relationship exists when each row in one table is associated with at most one row in another table.

One-to-one relationships can simplify JOIN behavior because the relationship normally does not multiply rows.

An EmployeeDetails table may contain one additional detail record for each employee.

SQL 
SELECT
    e.EmployeeID,
    e.EmployeeName,
    d.PersonalEmail
FROM Employees e
INNER JOIN EmployeeDetails d
    ON e.EmployeeID = d.EmployeeID;

Used for separating sensitive information, optional attributes, system modules, and normalized database structures.

  • Each side normally has at most one matching row.
  • Primary and unique keys can support one-to-one relationships.
  • JOINs normally preserve the expected row count.
  • Optional details may require LEFT JOIN.
  • One-to-one design can separate different types of information.
What is a one-to-one relationship?

It is a relationship where each record in one table corresponds to at most one record in the other table.

Which JOIN can preserve employees without detail records?

A LEFT JOIN can preserve employees even when no matching detail record exists.

Have you finished this lesson?

Introduction to SQL – All Lessons