I have two tables
A:
| ID |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
B:
| ID |
|---|
| 1 |
| 2 |
| 3 |
I need to introduce a column that marks if ID in A is present in B
| ID | flag |
|---|---|
| 1 | Y |
| 2 | Y |
| 3 | Y |
| 4 | N |
is there any way to do this without adding a flag column to B and coalescing with join?
coalesce(B.flag,'N') as flag
CodePudding user response：
You can use a LEFT JOIN and a CASE WHEN clause:
select
a.id,
case when b.id is not null then 'Y' else 'N' as flag
from
a left join b on a.id = b.id
CodePudding user response：
You are asking if something exists()...
select Id,
case when exists (select 0 from TableB b where b.Id = a.Id)
then 'Y' else 'N'
end as Flag
from TableA a;