Lesley's Digital Garden

join in SQL

A join operation combines columns from multiple tables into a new table. The operation corresponds to a join operation in relational algebra.

There are two kinds of joins in SQL, implicit and explicit join.

Implicit Join

To perform an implicit join, we add multiple tables after from. The join condition is defined using the where keyword, for example

select *
from Employee E, Department D
where E.departmentId=D.id;

Info

We don’t really need to qualify the name of the attributes in the above example. But we often need to do that if the 2 attributes in different tables share the same name. E.g.

select studentId
 from Student, Enrolled
 where studentId = studentId;

is not allowed. And we need to explicitly qualify the names as Student.studentId = Enrolled.studentId

This kind of join (like above) is called an Equi-join since the join condition tests for equality. The most frequently use cases for this kind of equi-join is to relate the value of the foreign key of one table to the same value of the corresponding primary key in the second table.

A common mistake is to forget about join condition. In that case, we have a cross join (also called cartesian join) where Cartesian product of the table is created:

select * from Employee, Department;

Implicit joins are concise but can be error prone for complex queries.

Explicit Joins

Explicit join uses the join keyword to join two tables and the on keyword to specify the join condition:

select *
from Employee E
join Department D on E.departmentId=D.id

Info

When we just use join, we are performing an “inner join” by default. We can also explicitly use inner join (same result)

More formally, join operators are specified in the from clause with both a join type and a join condition:

  • Join types: join, natural join, left outer join, right outer join, full outer join, cross join.
  • Join conditions: on <condition>, using (<list of attributes>).

Join Conditions

There are two syntax options specify the join condition: T1 join T2 on <condition>

  • Combines tuples from tables T1 and T2 whenever condition is true. T1 join T2 using ( field(s) )
  • Combines tuples from tables T1 and T2 whenever all listed fields(s) have the same values. Note that this assumes that these attributes occur with the same name.

Example of on

So far we have only shown equi-join, but we can use arbitrary join conditions. For example, below is a query to find all students who have worse grades than Bob (we can also achieve it via other means such as subqueries):

select S2.name, S2.gpa
from Student S1 join Student S2 on S2.gpa < S1.gpa
where S1.name = 'Bob'

Example of using

We can use inner join and using to combine 2 tables by checking whether tuples agree on specific attributes.

select studentId
 from Student S join Enrolled E using (studentId)
 where E.courseId = 42;

Info

The natural join operator is a shortcut for an inner join with using where the list of common attributes is automatically determined.

Natural Join

See also: natural join in relational algebra A natural join automatically combines tables based on columns with the same names and data types without an explicit join condition.

select studentId
 from Student S natural join Enrolled E
 where E.courseId = 42;

Assuming both tables have studentId, the above operation will return all students enrolled in course 42.

Warning

The use of natural join is risky in case where there are no common attributes (will also results in cartesian join). As this can happen through schema change, a good practice is to always use join ... using variant of this query.

Vs Explicit Equi-join

Compare natural join to an Equi-join like below, natural join will result in a table with only one studentId column, while Equi-join will duplicate the column.

-- Note, we can't just use studentId here since we have 2 different studentId
-- which will results in ambiguity
select S.studentId
 from Student S, Enrolled E
 where S.studentId = E.studentId and E.courseId = 42;

On the other hand, natural join can be considered a special case of an equi-join. Equi-join is more powerful, and can match columns with different names (e.g. S.id = E.student_id).

Outer Joins

Inner joins combine tuples whenever the join condition is true. If there is no join partner available on one side, there is also no result tuple produced.

Outer join produces a resulting join tuple even if there is no join partner available in one of the input tables. The result tuple is filled with NULL values on the corresponding side.

There are three variants of outer join:

T1 left outer join T2

  • Keep all input tuples of T1 in the result even if there is no join partner. Fill with NULLS on the right side if required. T1 right outer join T2
  • Same as above, but keep all input tuples from T2 T1 full outer join T2
  • Keep all input tuples from both

Below is an example to list all students and their enrollment (even for students who don’t enroll in any courses):

select studentId, courseId
  from Student
       left outer join Enrolled using (studentId)
 order by studentId, courseId;

In general, T1 left outer join T2 can also be expressed as T2 right outer join T1:

select studentId, courseId
  from Enrolled
       right outer join Student using (studentId)
 order by studentId, courseId;

See Also

Graph View

Backlinks