3 ms·

It's a nice visualization, but it assumes the keys on both sides are unique. Usually the key on at least one side of a join is not unique, and sometimes it's no

It's a nice visualization, but it assumes the keys on both sides are unique. Usually the key on at least one side of a join is not unique, and sometimes it's not unique on either side. The behaviour in these cases is an important part of the behaviour of the overall join.

da_chicken 5y ago
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.
- Diggsey 5y ago
  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.

da_chicken 5y ago

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.

Diggsey 5y ago
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.