MYSQL INNER JOIN可能有空字段 [英] MYSQL INNER JOIN with possible empty fields

问题描述

好的，我在连接2个表(使用INNER JOIN)时遇到问题.第一个表格包含类别列表(例如相册或博客帖子的类别)，而第二个表格包含数据"

Ok, I have a problem with joining 2 tables (with INNER JOIN). First table containts categories list (ecc. for photo albums, or blog posts) while the second table contains "the data"

我尝试过这样:

SELECT galeries_id, galeries_title, 
       photos.photos_id, photos.photos_gal_id, photos.photos_link 
FROM galeries 
INNER JOIN photos 
ON galeries.galeries_id=photos.photos_gal_id 
GROUP BY photos_gal_id

这给了我一个公平的结果，很好地加入了我的桌子，就像我希望他们有一个关键的表现一样.

This gives me fair result, joining my tables nicely, just as I want them to with one crucial exeption.

如果表"photos"没有包含"photos_gal_id"的行(例如"2")，则即使该表在galeries表中执行egzist，也不会返回该类别的任何类别(galeries_id，galeries_title)

If table "photos" doesn't have a row which contains "photos_gal_id" (for example "2"), than it will NOT return any category (galeries_id, galeries_title) for that category even if it does egzist in galeries table.

之所以合乎逻辑，是因为:

It is logical because of:

 ON galeries.galeries_id=photos.photos_gal_id

现在我需要采用这一部分来向我展示第二张表中没有相关行的那些东西

Now I need to adopt this part to show me even thoes which doesn't have a related row in the second table

我想要得到的结果是:

galeries_id      galeries_title       photos_id       photos_link
     1               blabla              3            test.jpg
     2               bla bla2                                     
     3               etata               5            test.jpg

我希望我对它的解释足够好:) 谢谢.

I hope I explained it good enough :) Thank you..

推荐答案

要保留照片中没有匹配ID的galeries中的行，您将需要使用LEFT JOIN(而不是INNER JOIN)将照片加入画廊中:

To retain the rows from galeries with no matching ID in photos, you will need to join photos on galeries with LEFT JOIN instead of INNER JOIN:

SELECT galeries_id, galeries_title, 
       photos.photos_id, photos.photos_gal_id, photos.photos_link 
FROM galeries 
LEFT JOIN photos 
ON galeries.galeries_id=photos.photos_gal_id 
GROUP BY photos_gal_id

 

这将为您提供:

galeries_id      galeries_title       photos_id       photos_link
     1               blabla              3            test.jpg
     2               bla bla2            NULL         NULL                
     3               etata               5            test.jpg

 

如果您希望将NULL替换为空字符串，则可以使用:

And if you wish to replace NULL with an empty string, you can use:

SELECT
  IFNULL(photos.photos_id, ''),
  IFNULL(photos.photos_link, '')

这篇关于MYSQL INNER JOIN可能有空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！