Is this SQl Considered a JOIN?

Question

I am using MySQl to category name from 1 table based on the Category ID in a "Module" table.

I have the below SQL working fine for my needs but I am wanting to know if this is considered a JOIN or not?

Since it does not call a JOIN ?

SELECT  `mo_category_fk` ,  `mo_name_vc` ,  `mc_name_vc`
FROM x_modcats mc, x_modules m
WHERE mc.mc_id_pk = m.mo_category_fk
AND m.mo_folder_vc =  :module

Yes, you are joining on the tables. However, I personally prefer using the INNER JOIN syntax... — sgeddes, Mar 17 '13 at 01:08
BTW -- this might be useful... http://stackoverflow.com/a/1018825/1073631 — sgeddes, Mar 17 '13 at 01:09

score 3 · Answer 1 · answered Mar 17 '13 at 01:09

Yes - In MySQL implicit and explicit joins have identical execution plans. You can verify this with EXPLAIN. But here is a sample from another thread:

mysql> explain select * from table1 a inner join table2 b on a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL | NULL    | NULL         |  986 |       |
|  1 | SIMPLE      | a     | ref  | pid           | pid  | 4       | schema.b.pid |   70 |       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.02 sec)

mysql> explain select * from table1 a, table2 b where a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL | NULL    | NULL         |  986 |       |
|  1 | SIMPLE      | a     | ref  | pid           | pid  | 4       | schema.b.pid |   70 |       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.00 sec)

score 2 · Accepted Answer · edited Mar 23 '13 at 17:42

Yes you are joining. Per the documentation, , can be used as a substitute for the JOIN keyword .. except you can't use the very helpful ON clause. However, you have a condition that connects the tables in the WHERE clause. In my opinion, it makes more sense to do it as part of the FROM clause:

SELECT mo_category_fk, mo_name_vc, mc_name_vc
FROM x_modcats mc 
JOIN x_modules m ON (mc.mc_id_pk = m.mo_category_fk)
WHERE m.mo_folder_vc = :module

Is this SQl Considered a JOIN?

2 Answers2