英文:
How to get a row even if it fails inner join condition?
问题
我们有一个场景，我们希望获取满足内连接条件的所有数据，即使它们不符合连接条件，也要获取另一条记录。
为了包括这一条不匹配的记录，我们有主键值，我们不能应用左连接，因为它会包括左表中的所有不匹配的记录，但我们只对其中的一条感兴趣。
例如，我们有一个主键值 Id = 3。
表1：
Table1_ID Table1_Name
1 Test
2 Test_1
3 Test_2
表2：
Table2_ID Table2_Name
1 Test
期望的结果：
ID Name
1 Test
3 Test2
英文:
We have a scenario where we want all the data which satisfies inner join also one more record even if it fails join condition.
To include that one unmatched record we have primary key value, we can not apply Left join coz it include all the unmatched records from left table but we are interested in only 1.
ex. We have a primary key value Id = 3.
Table 1:
Table1_ID Table1_Name
1 Test
2 Test_1
3 Test_2
Table 2:
Table2_ID Table2_Name
1 Test
Expected result:
ID Name
1 Test
3 Test3
答案1
得分: 3
你可以尝试使用 LEFT JOIN，然后在 WHERE 条件中筛选不匹配的记录：
SELECT t1.Table1_ID AS ID, t1.Table1_Name AS Name
FROM Table1 t1
LEFT JOIN Table2 t2 ON (t1.Table1_ID = t2.Table2_ID)
WHERE (t2.Table2_ID IS NOT NULL OR t1.Table1_ID = 3)
在这里查看演示：链接
英文:
You could try to use LEFT JOIN, then filter un-matched records in WHERE conditions
SELECT t1.Table1_ID AS ID, t1.Table1_Name AS Name
FROM Table1 t1
LEFT JOIN Table2 t2 ON (t1.Table1_ID = t2.Table2_ID)
WHERE (t2.Table2_ID IS NOT NULL OR t1.Table1_ID = 3)
See demo here
答案2
得分: 0
你可以使用union将两个查询视为一个。
select t1.*
from table1 t1
join table2 t2 on t1.id = t2.id
union
select *
from table1
where id = 3
英文:
You can use a union to treat two queries as one.
select t1.*
from table1 t1
join table2 t2 on t1.id = t2.id
union
select *
from table1
where id = 3
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论