I have 2 or more tables that will frequently be used for JOINs in database VIEWs but they don't have any field in common so I'm considering adding the same generated id key to be inserted into a "join_id" field of 3 different tables to be joined later. I searched the internet regarding this type of topic but don't see much discussion so I'm cautious if it's good practice or not. Is it safe to proceed to do this kind of database operation?


Here's more details on what I want to accomplish


DEPT-A has their own table They will create a new row/record with flds1, flds2, flds3, innerjoin_id

DEPT-B has their own table with flds4, flds5, flds6, innerjoin_id

When DEPT-A creates a new row, a rule will be triggered to create a new row with same innerjoin_id in DEPT-B table also.

DEPT-B needs to see DEPT-A flds1, flds2, flds3 and be able to edit their respective flds4, flds5, flds6.

DEPT-A web input form will see flds1, flds2, flds3 (editable)

DEPT-B web input form will see flds1-readonly, flds2-readonly, flds3-readonly, flds4-editable, flds5-editable, flds6-editable


DEPT-A enters "apple", "banana", "cherry"

DEPT-B needs to see apple, banana, cherry and then in fld4-6 enter "applebanana", "bananacherry", "cherryapple"

DEPT-B is dependent on DEPT-A flds1-3

If DEPT-B cannot see "apple","banana","cherry" then they don't know what to input for flds4, flds5, flds6

2 Answers 2


Specifically for MySQL...

When you JOIN two tables, you specify how the tables are related.

  • (The common case.) ... FROM cities JOIN countries ON countries.id = cities.country_id -- This a many:1 relationship (many cities in one country).
  • (Rare.) ... FROM a CROSS INNER JOIN b (without anything relating the two tables) -- This gives you all combinations of the rows of a and of b. In MySQL, the keyword CROSS is optional.
  • (Sometimes) ... FROM a LEFT JOIN b ON ... WHERE b.id IS NULL -- This finds the rows of a for which the corresponding row of b is missing.

ON and WHERE have the same semantics for INNER JOIN and CROSS JOIN. But it helps the reader to use ON to show the relationship (whether or not there is a FOREIGN KEY) and to use WHERE for 'filtering'.

Any number of tables can be JOINed together, in SELECT, in a VIEW, in a UPDATE, or in a DELETE.

A VIEW is syntactic sugar for a SELECT. Essentially all rules that apply to one apply to the other.

To one of your key questions: There is no requirement that a column be in 'common' to JOIN on them. Can you guess what results from this strange JOIN:

SELECT us_states.state_name 
    FROM us_states
    JOIN countries ON us_states.state_name =

Hint: The one answer begins with 'G'.

  • Great explanation! Thanks for the insight on JOINs and VIEWs. I edited the OP to include an example of how to tables will be used. Is there a way to accomplish this using good practices or my method is ok? Sep 24, 2022 at 4:41

Selective permissions cannot be done with JOIN.

Use VIEWs to allow certain users (based on login to MySQL) to write vs read-only vs not-even-see columns of table(s).

Use GRANTs to the users on specific columns of specific tables.

You can probably handle readonly with Grants. But to allow a user to see only certain columns may require separate Views.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.