如何基于另一个表的值在SQL表上创建列

I currently have 2 SQL tables: table1 and table2. table1 has 2 columns called id_a and column_a. table2 has 2 columns called id_b and column_b.

我目前有2个SQL表：table1和table2。 table1有2列，名为id_a和column_a。 table2有2列，名为id_b和column_b。

I would like to create a new_column in table1. The value of new_column should be 1 if the value of table1.id_a exists in table2.id_b. Otherwise, new_column should have the value of 0.

我想在table1中创建一个new_column。如果table1.id_a的值存在于table2.id_b中，则new_column的值应为1。否则，new_column的值应为0。

How do I do this? I'm using SQLite3 and table1 is significantly larger than table2.

我该怎么做呢？我正在使用SQLite3，而table1明显大于table2。

3 个解决方案

#1

How about a simple IN?

一个简单的IN怎么样？

SELECT *,
       id_a IN (SELECT id_b FROM table2) AS flag
FROM table1;

#2

How about a simple exists?

一个简单的存在怎么样？

select t1.*,
       (case when exists (select 1 from table2 t2 where t2.id_b = t1.id_a)
             then 1 else 0
        end) as flag
from table1 t1

#3

A left join solution for fun! In certain circumstances might even be more efficient than correlated subquery as well.

一个有趣的左连接解决方案！在某些情况下，甚至可能比相关子查询更有效。

select
    t1.*,
    case 
        when t2.id_b is null then 0
        else 1
    end as new_column
from
    table1 t1
left join
    table2 t2
    on t2.id_b = t1.id_a

#1