如何基于另一个表的值在SQL表上创建列(How to create a column on a SQL table, based on values of another table)

如何基于另一个表的值在SQL表上创建列(How to create a column on a SQL table, based on values of another table)

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

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

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

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.

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.

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

最满意答案

一个简单的IN怎么样？

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

How about a simple IN?

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