Great! So far, even when joining tables on non primary-foreign key values, we've always used the equality operator. But that's not the only conditional operator we can use. In fact, we can use any condition to join two tables, such as comparison operators (
BETWEEN operator or any other logical condition to join tables.
Suppose we have two tables:
subject table contains all subjects offered at a university. The
student table contains student data and information about the subjects they are currently studying.
For each student, we would like to show the subjects they could switch to. To do this, we show each student's name together with the name of every subject that they are not currently studying:
ON student.subject_id != subject.id
Every row from the
student table is joined with every row from the
subject table except for the subject the student is already studying.