Which is better JOIN or using multiple tables in a query?

Question

MySQL

In short, I was playing with the mask (MySQL) here and noticed this thing: The
query
SELECT product.maker, pc.price
FROM product, PC
WHERE product.model = pc.model
Returns the same data as
SELECT maker, price FROM Product JOIN PC
ON PC.model = Product.model Tell me
, what is the difference between these two options for executing a command? Are there any performance differences?

Answer 1

mysql> explain select test6.i from test6, test9 where test6.i=test9.i;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test6 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | test9 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0,00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`test6`.`i` AS `i` from `test`.`test6` join `test`.`test9` where (`test`.`test9`.`i` = `test`.`test6`.`i`) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> explain select test6.i from test6 join test9 on test6.i=test9.i;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test6 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | test9 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0,00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`test6`.`i` AS `i` from `test`.`test6` join `test`.`test9` where (`test`.`test9`.`i` = `test`.`test6`.`i`) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

As you can see, both styles are rewritten into the same representation.
It makes sense to use join on for human readability reasons. such a join immediately tells which fields the tables are linked by, and where organically remains for filtering.
Plus, if you have a connection with a pair of fields of the same name, you can write
Which is equivalent to PC.model = Product.model, but shorter and allows you to refer simply to model in the future - the parser already knows that the field value will be identical and will not guess whether you wanted to get model from PC or from Product

Answer 2

There are good videos on youtube, including in Russian, which clearly show the difference between queries from all tables and joins. The difference in speed there is colossal and it clearly shows why.

Answer 3

JOIN is better
in general there is a report about MySQL internals, where it is shown that JOIN works very fast with the processor (to keep only the final table in memory, and not all source tables), so JOIN is not at all what it seems / is drawn