sqlmysqljoinequality

Compare two tables in SQL and set an output column to 'True' if column values are equal

Can someone derive and explain an SQL query for the below outcomes in TableResult?

TableA                     TableB                    TableResult

Id | Function              Id | Function              Id | Function | Compare
---|----------             ---|----------            ----|----------|---------
1  | code1                 1  | code1                 1  | code1    | true
2  | code2                 2  | code4                 2  | code2    | false
3  | code3                 3  | code5                 3  | code3    | false
4  | code4                                            4  | code4    | true

There are two tables, TableA and TableB. The Function column in each table is unique.

The entire data in TableA should be included in ResultTable. If TableB Function equals the TableA function, then the ResultTable Compare column must be true; otherwise it's false.

How can the desired outcome be achieved in SQL?

Solution

  • You can do it with EXISTS:

    select a.*,
  exists (select 1 from TableB b where b.function = a.function) compare
from TableA a

    or:

    select a.*,
  case 
    when exists (select 1 from TableB b where b.function = a.function) then 'true'
    else 'false'
  end compare
from TableA a

    See the demo.