MySQL/PHP连接表而不必总是有公共列 [英] MySQL / PHP Joining tables without always having common column

问题描述

更新 感谢您为我的原始问题提供的所有答案. 我现在有另一个与这些查询有关的问题.加入表后，我想搜索NULL结果，在下面的示例中，我想搜索surname = NULL/surname =''，但是它返回一个空结果，即使有2个未链接的结果也是如此.

UPDATE Thanks for all the answers for my original question. I now have another problem related to these queries though. After I join the tables I would want to search for NULL results, following my example below I would like to search for surname=NULL/surname='', but it returns an empty result, even though there are 2 non-linked ones.

查询示例:

SELECT table1.*,table2.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.surname=NULL

这将返回0.

有什么想法吗?我可以理解为什么它返回0的逻辑，但是必须有一种方法使它起作用(?)

Any ideas? I can understand the logic behind why it would return 0, but there must be a way to get this to work (?)

原始问题: 我找不到我的问题的答案，可能是因为我不知道我应该寻找什么.

Original Question: I can't find any answers on my question and probably it's because I don't know what I should be looking for.

我遇到的问题是我想用一个公用指示符连接两个表 ，但是要包含不存在该公用指示符的记录.

The problem I have is that I want to join 2 tables with a common indicator but include records where the common indicator also doesnt exist..

也许我没有任何意义，但我会尝试在下面的代码中进行解释

Maybe I'm making no sense, but I'll try to explain in code below

表1

id | name
1 | John
2 | Michael
3 | Anna
4 | Sue

表2

id | surname
1 | Doe
2 | Anderson

所以我想要一个输出/绑定表的查询，如下所示:

So I would like a query that outputs/binds the tables like so:

table1.id | table1.name | table2.id | table2.surname
1 | John | 1 | Doe
2 | Michael | 2 | Anderson
3 | Anna | NULL | NULL
4 | Sue | NULL | NULL

因此，请在有数据的地方合并，而在没有数据的地方不要合并(或添加NULL值). table2的数据总是少于table1，因此table1可以用作骨骼. vlookup可以在Excel中完美地执行此操作，并在找不到该值的情况下返回N/A 我知道如何使用PHP来实现这一点，但是在处理超过50k的记录时，我想加快我的代码的速度，我不想单查询每一行来查找另一个表列.

So, merge where there is data, and dont merge where this is no data (or add NULL value). The table2 will always have less data than table1, so table1 could be used as a bone. vlookup does this perfectly in Excel, returns N/A where the value is not found I know how to achieve this with PHP but I'm trying to speed up my code, when working with records well above the 50k I don't want to single query every row for another table column.

我尝试了WHERE table1.id = table2.id，但是只有在table2.id存在的情况下它才加入它们，我也尝试了JOIN ON函数，但是它的作用相同.

I've tried the WHERE table1.id=table2.id, but it only joins them if table2.id exists, I've also tried the JOIN ON function, but it does the same.

请帮助

推荐答案

也许使用LEFT JOIN.

Maybe use LEFT JOIN.

SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id

请参见这篇出色的Jeff Atwood的帖子.

这篇关于MySQL/PHP连接表而不必总是有公共列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！