How to do a query where a new column is added with with match results?

How can I do a query where I fetch all items from table1 and than I compare each of table1 column2 items with table2 column2 items?

The final query would contain an extra column3 with a value 1 or 0 if a matched was found. Please see below example tables and expected result.

• table1:

  _id  col1     col2
  ---  -------  ----
  0    Jon      25
  1    Tim      24
  2    Frank    38
  3    Josh     234
  4    Lettuse  23
  5    Whally   12
  

• table2:

  _id  col1   col2
  --- 开发者_运维问答 -----  ----
  0    House  45
  1    Dog    23
  2    Pat    24
  3    Lake   123
  4    Water  43
  5    Hot    2
  

• newTable1Results:

  _id  col1     col2  col3
  ---  -------  ----  ----
  0    Jon      25    0
  1    Tim      24    1
  2    Frank    38    0
  3    Josh     234   0
  4    Lettuse  23    1
  5    Whally   12    0
  

I think you're looking for something like this:

select t1._id, t1.col1, t1.col2, t2.col2 is not null as col3
from table1 t1
left outer join table2 t2 on t1.col2 = t2.col2
order by t1._id

SQLite's booleans are just zero and one so a simple t2.col2 is not null in your SELECT will give you your zero or one.

The above should give you this:

_id | col1   |col2 | col3
----+--------+-----+-----
  0 | Jon    |  25 |    0
  1 | Tim    |  24 |    1
  2 | Frank  |  38 |    0
  3 | Josh   | 234 |    0
  4 | Lettuse|  23 |    1
  5 | Whally |  12 |    0

You could use LEFT JOIN and compute the col3 column based on whether there was a match:

SELECT
  t1.*,
  CASE
    WHEN t2._id IS NULL THEN 0
    ELSE 1
  END AS col3
FROM table1 t1
  LEFT JOIN table2 t2 ON t1.col2 = t2.col2

In case table2.col2 could possibly contain duplicates, the above query might give you duplicates as well. You could try a different approach then:

SELECT
  t1.*,
  CASE
    WHEN EXISTS (
      SELECT *
      FROM table2
      WHERE col2 = table1.col2
    )
      THEN 1
    ELSE 0
  END AS col3
FROM table1