SQL Relationships

• Assume a table of employees with a field holding the department number

• Assume a table of departments mapping the number to the name

SELECT e.name, d.name

FROM employees e INNER JOIN departments d

ON e.dept_id = d.dept_id;

Alice,Operations

Bob,Operations

Carol,Administration

Notes:

Might see some other syntaxes. This is SQL92. Easier to read with

multiple tables and higher complexity. But database engines support

the older syntax too and it is often seen.

The dept_id here is a "foreign key". That is the relationship here.

It references the id of another record. (Another record in either

another table or the local table. It is perfectly general.)

Can join many more tables than just two.

Often a "join table" will be used to do nothing more than to hold the

relationship between two items in a Has-And-Belongs-To-Many (HABTM)

relationship.

You can join the same table multiple times.

You can join the same table to itself in a self-join.