sql join告诉我其他表中是否存在ID [英] sql join which tells me if ID exists in other table

问题描述

我有2张桌子:

 A      B
 --    ----
 ID    FKID
 --    ----
 1      3
 2      3   
 3      4
 4      4

我需要一条select语句，该语句向我显示A的全部内容，并显示一个字段，该字段告诉我表B是否具有与该ID匹配的ID.

I need a select statement which shows me all of A with a field that tells me if table B has any ids that match that ID.

Desired Result
-----------
 ID | hasB
-----------
 1    no
 2    no    
 3    yes
 4    yes

推荐答案

在SQL Server中，这是最有效的方法，而不是先执行OUTER JOIN，然后使用DISTINCT删除重复项.不确定Postgres，您需要检查计划.

In SQL Server this would be the most efficient way rather than doing an OUTER JOIN then removing the duplicates with DISTINCT. Not sure for postgres, you'd need to check the plans.

SELECT ID,
       CASE
         WHEN EXISTS (SELECT *
                      FROM   B
                      WHERE  B.FKID = A.ID) THEN 'yes'
         ELSE 'no'
       END AS hasB
FROM   A  

这篇关于sql join告诉我其他表中是否存在ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！