CODEWITHSUNDEEP
sqljoinsemantics
nathanallen
nathanallen

Reputation: 424

Is a "join table" the result of a SQL JOIN, or the table between a many-to-many

This is a question about correctly "naming things". Specifically, how do you distinguish between:

  1. the "between" table in a many-to-many relationship (e.g. users, users_questions, questions)
  2. the (temporary) table that is created during a SQL JOIN (e.g. 'SELECT * FROM users INNER JOIN users_questions.user_id ON users.id WHERE users_question.question_id=37016694;`)

Upvotes: 5

Views: 11430

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

From relational point of view, a JOIN table (a table which resolves many-to-many relationship) is real physical table. Else it wouldn't survive between requests. In my opinion, the term "join table" was coined by MVC "Code First" developers who ignore physical entities in the DB realm, especially if they are not shown in DbContext.

In my opinion, again, we should honor relational realities.

So help me Codd.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108796

Lots of database designers use the term join table in your first sense: to implement a many-to-many relationship between entities. It's also called a junction table, association table, and other things. More info: https://en.wikipedia.org/wiki/Associative_entity

I've never heard the second sense used. (But, hey, I don't get out much. :-) If you're writing documentation, or teaching, I suggest you reserve the word table to mean an actual, physical, table. Avoid using the word table for a resultset unless you qualify it by saying virtual table or some such phrase. That way your readers and students won't waste time trying to find the definitions of these not-really-tables in your schema.

Upvotes: 19

Related Questions