How to get a row even if it fails inner join condition?

wixjitnu 于 2023-02-28 发布在其他

关注(0)|答案(2)|浏览(130)

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

sql-server

来源：https://stackoverflow.com/questions/75552515/how-to-get-a-row-even-if-it-fails-inner-join-condition

2条答案

按热度按时间

lf3rwulv1#

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

赞(0）回复(0）举报 2023-02-28

bvjxkvbb2#

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

赞(0）回复(0）举报 2023-02-28

我来回答

How to get a row even if it fails inner join condition?

2条答案

相关问题

热门标签

最新问答