MySQL left JOIN，如果没有设置所有行则结果不起作用

Question

我收到以下查询。这应该返回一行，但事实并非如此。

不幸的是，什么也没有。

当我删除最后一个连接子句时:

LEFT JOIN `serene_pagecontent` ON `serene_pageorder`.`pageorder_id` = `serene_pagecontent`.`pagecontent_link`

我确实得到了结果。但添加后就不行了。据我所知，即使缺少其中一个字段，在 join 之前添加 LEFT 也应该会产生结果。

SELECT * FROM `serene_page`
LEFT JOIN `serene_pageoptions` ON `serene_page`.`page_id` = `serene_pageoptions`.`pageoptions_link`
LEFT JOIN `serene_pagetitle` ON `serene_page`.`page_id` = `serene_pagetitle`.`pagetitle_link`
LEFT JOIN `serene_pageorder` ON `serene_page`.`page_id` = `serene_pageorder`.`pageorder_link`
LEFT JOIN `serene_pagecontent` ON `serene_pageorder`.`pageorder_id` = `serene_pagecontent`.`pagecontent_link`
WHERE `page_id` = 34 AND `pageoptions_language` = 'NL' AND `pagetitle_language` = 'NL' AND `pagecontent_language` = 'NL'

谁能告诉我我错过了什么。

编辑

WHERE `serene_page`.`page_id` = 34 
  AND serene_pageoptions`.`pageoptions_language` = 'NL' 
  AND serene_pagetitle`.`pagetitle_language` = 'NL' 
  AND serene_pagecontent`.`pagecontent_language` = 'NL'

EDIT SOLUTION : solution by "juergen d"

     SELECT * FROM `serene_page`

     LEFT JOIN `serene_pageoptions` ON `serene_page`.`page_id` =`serene_pageoptions`.`pageoptions_link` AND
 `serene_pageoptions`.`pageoptions_language` = 'NL'       
  LEFT JOIN `serene_pagetitle` ON `serene_page`.`page_id` = `serene_pagetitle`.`pagetitle_link` AND
 `serene_pagetitle`.`pagetitle_language` = 'NL'     

     LEFT JOIN `serene_pageorder` ON `serene_page`.`page_id` = `serene_pageorder`.`pageorder_link` 


     LEFT JOIN `serene_pagecontent` ON `serene_pageorder`.`pageorder_id` =
 `serene_pagecontent`.`pagecontent_link` AND
 `serene_pagecontent`.`pagecontent_language` = 'NL'

     WHERE `serene_page`.`page_id` = 34 

I wasn't aware of the fact that I had to add the subclause if not it would turn to normal JOINS. Thanks!!

Answer 1

如果左连接，您需要将连接表的条件放入 JOIN 中。否则 LEFT JOINS 会变成 INNER JOIN

SELECT * 
FROM serene_page s
LEFT JOIN serene_pageoptions po  ON s.page_id = po.pageoptions_link
                                AND po.pageoptions_language = 'NL' 
LEFT JOIN serene_pagetitle pt    ON s.page_id = pt.pagetitle_link
                                AND pt.pagetitle_language = 'NL' 
LEFT JOIN serene_pageorder o     ON s.page_id = o.pageorder_link
LEFT JOIN serene_pagecontent c   ON o.pageorder_id = c.pagecontent_link
                                AND c.pagecontent_language = 'NL'
WHERE s.page_id = 34