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 (<, >, <=, >=, !=, <>), the BETWEEN operator or any other logical condition to join tables.

Suppose we have two tables: Student and Subject. The 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:

SELECT
  Student.Name,
  Subject.Name,
FROM Student
JOIN Subject
  ON Student.SubjectId != 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.