JOIN is a key concept in the
relational model and applies to pretty much any multi-table query. JOIN is "product", or multiplication in the
relational algebra.
Relational algebra is to relations as arithmetic is to numbers. What we call "tables" are really "relations" from SET theory, and SQL implements (somewhat poorly) the algebra which operates on them. To use it effectively you need to learn the relational algebra much as you must learn arithmetic to balance your checkbook. It really isn't difficult and more than worth the effort!
JOIN does the heavy lifting, WHERE is like a filter to apply after the fact, if necessary.
When you find yourself attempting to express something with WHERE which becomes very complex or doesn't seem to do what you want, it is very often because you are using the wrong operator! Kind of like trying to write an algebraic equation with only addition and subtraction, but without using multiplication! Sure, you could do it, but what a mess!
INSERT does not support multi table queries in MySQL/Mariadb and it is difficult to imagine an INSERT ... JOIN even conceptually.
MySQL JOIN Syntax - Note the first statement on that page:
Quote:
|
MySQL supports the following JOIN syntax for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements:
|
Now see how that applies to these...
MySQL UPDATE Syntax
MySQL SELECT Syntax
MySQL INSERT Syntax
Good luck!
