SELECT * FROM `blog_post` WHERE `id` IN ( SELECT id_post FROM `key_post` WHERE `id_tag` IN ( SELECT id FROM `tag_post` WHERE `tag` = 'вася' ) ) AND `status` = 1 ORDER BY `id` DESC LIMIT 0, 30;

and

 SELECT * FROM `blog_post` LEFT JOIN `key_post` ON `key_post`.`id_post` = `blog_post`.`id` LEFT JOIN `tag_post` ON `tag_post`.`id` = `key_post`.`id_tag` WHERE `tag_post`.`tag` = 'вася' AND `blog_post`.`status` = 1 ORDER BY `blog_post`.`id` DESC LIMIT 0, 30;

Will the result be the same for these queries?

  • one
    Maybe yes maybe no. Depends on data. LEFT JOIN selects lines for which id is in key_post with the addition of fields from key_post and lines for which key_post.id_post does not exist. In the first version, these lines will not be included in the sample. The same with tag_post. In the second version (well, I don’t remember - sorry) select * from means all fields from blog_post, all fields from three tables. Specify yourself. - alexlz
  • > The result will be the same for these requests? if you have tablets at hand, why not try to find out for yourself? - Viacheslav
  • And what does EXPLAIN SELECT ... give? - Sergiks

1 answer 1

Queries will give equivalent results if you select only fields from blog_post in the second query.

 SELECT `blog_post`.*

In addition, there is no reason for using LEFT .