What is the point of providing a JOIN condition when there are foreign keys?

Question

TL;DR: Why do we have to add ON table1.column = table2.column?

---

This question asks roughly why do we need to have foreign keys if joining works just fine without them. Here, I'd like to ask the reverse. Given the simplest possible database, like this:

CREATE TABLE class (
  class_id INT PRIMARY KEY,
  class_name VARCHAR(40)
);

CREATE TABLE student (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(40),
  class_id INT,
  FOREIGN KEY(class_id) REFERENCES class(class_id) ON DELETE SET NULL
);

… and a simple join, like this:

SELECT student_id, student_name, class_name
FROM student
JOIN class
ON student.class_id = class.class_id;

… why can't we just omit the ON clause?

SELECT student_id, student_name, class_name
FROM student
JOIN class;

To me, the line FOREIGN KEY(class_id) REFERENCES class(class_id) … in the definition of student already includes all the necessary information for the FROM student JOIN class to have an implicit ON student.class_id = class.class_id condition; but we still have to add it. Why is that?

Answer 1

^{Reposting Damien_The_Unbeliever's comment as an answer}

• you don't have to join on foreign keys;
• sometimes multiple foreign keys exist between the same pair of tables.

Also, SQL is a crusty language without many shortcuts for the most common use case.