5

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;`)
nathanallen
  • 424
  • 3
  • 10
  • It is a table that establishes the "join" relationship between the other tables. The result of a query is a result set (and *not* a table); a temporary table / storage in a table variable / loading into a data table is different than a result set. – user2864740 May 04 '16 at 00:53
  • The first is a real table which helps to normalize your database. The second is temporary and is not part of your actual schema. – Tim Biegeleisen May 04 '16 at 00:53

2 Answers2

18

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I'm not even sure what he means in the 2nd sense -- he has a query with a join -- where is the temporary table? – Hogan May 04 '16 at 00:58
  • I've most commonly heard the junction table, or the "between" table, referred to as a linking table. – Jeff Puckett May 04 '16 at 01:00
  • I've always referred to the "between table" as being a cross reference table, sometimes prefixed by "Xref" like "Xref_User_Questions" – Robert McKee May 04 '16 at 01:30
  • 1
    @Hogan . . . I think the second sense refers to the relational ideas that inner joins are subsets of the Cartesian product. So the "join table" is the Cartesian product of the two tables and the join specifies the subsetting of the relationship. – Gordon Linoff May 04 '16 at 01:31
  • @GordonLinoff -- OK, I can buy that there is an abstract concept of a table potential in the join, but the OP mentions a table being created. I think that is taking it a little to far -- it is conceived of not actually created. – Hogan May 04 '16 at 03:43
0

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.

Alex Kudryashev
  • 9,120
  • 3
  • 27
  • 36