I understand what you're driving at and I think you know this, but your terminology sounds confused to me. I don't think you're wrong, I just wouldn't phrase things the way you have.
At least in RDBMS terms it's not a key if it's not unique. "Key" means "a set of columns whose values uniquely identify a record in the table".
You might have a column which is a key in another table, a.k.a., a foreign key. But a foreign key is [nearly always] not itself a key in the table it's foreign in. You might also have a composite key with a subset of fields that are foreign keys, too. But I wouldn't describe a foreign key as a key. It's unique in another table but not unique to this one. It's a key there, but not a key here.
I would say that in nearly all cases in a well-design system, a proper JOIN condition is going to involve the key of either the left or right table. It's almost always incorrect not to have at least one JOIN condition involve a key. You'll usually know when not to do that because you know you're intentionally ignoring the data model.
I do agree that the visualization doesn't adequately communicate the row "duplication" that often happens and which new SQL users often find confusing or undesirable.
Yes, perhaps "key" is the wrong term. I intended to refer to the column that is used in the join condition in not so many words.
> It's almost always incorrect not to have at least one JOIN condition involve a key.
Eh, almost always seems a little strong. Sometimes you can reduce the number of joins you need to do by "skipping" a table that would serve as the unique key on both sides of the join.
ie. instead of joining:
`A --[many-to-one]--> B <--[one-to-many]-- C`
You can directly join on `A.b_id = C.b_id`, and that's perfectly valid.
At least in RDBMS terms it's not a key if it's not unique. "Key" means "a set of columns whose values uniquely identify a record in the table".
You might have a column which is a key in another table, a.k.a., a foreign key. But a foreign key is [nearly always] not itself a key in the table it's foreign in. You might also have a composite key with a subset of fields that are foreign keys, too. But I wouldn't describe a foreign key as a key. It's unique in another table but not unique to this one. It's a key there, but not a key here.
I would say that in nearly all cases in a well-design system, a proper JOIN condition is going to involve the key of either the left or right table. It's almost always incorrect not to have at least one JOIN condition involve a key. You'll usually know when not to do that because you know you're intentionally ignoring the data model.
I do agree that the visualization doesn't adequately communicate the row "duplication" that often happens and which new SQL users often find confusing or undesirable.