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

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?

CodePudding user response：

For this you must consider the JOIN operation. It doesn't check if your two table or collection have relation or not. So the simple join without condition (ON) you will have a big result with all possibilities.
The ON operation filters to get your expected result

CodePudding user response：

JOIN condition is an expression which specifies the maching criteria, and it is checked during JOIN process. It can cause a fail only if syntax error occures.

FOREIGN KEY is a rule for data consistency checking subsystem, and it is checked during data change. It will cause a fail if the data state (intermnediate and/or final) does not match the rule.

In other words, there is nothing in common between them, they are completely different and unrelated things.