Search code examples
sqljoinsemantics

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;`)
Solution

  • 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.