sql - 检查列是否存在于另一个表中

Question

表格1

customer   item  price
Andy       Doll   50
Bella      Robot  25

表_2

customer_name  address 
Andy           Baker Street, London
Carlos         Huntington Street, Newcastle

我想根据 table_1 中的值是否存在于 table_2 中创建一个新列。预期产出

customer.   exists_in_table_2
Andy            Yes
Bella           No

到目前为止我的代码：

select customer, 
case when customer in (select customer_name from table_2) then 'Yes'
else 'No' end as exists_in_table_2

有没有更有效的方法呢？

标签： sqlpostgresql

Answer 1

查询的效率取决于您使用的数据库。我通常建议exists使用适当的索引。那将是：

select t1.*, 
       (case when exists (select 1 from table2 t2 where t1.customer = t2.customer_name)
             then 'Yes' else 'No'
        end) as exists_in_table_2
from table1 t1;

适当的索引是 on table2(customer_name)。