Gets all the records from Table A if the id field does not exist in table B and if it exists it is d-CodePudding

I need to get a query from two tables that is conditional. I want to say, I need to obtain the "id_product" field from table A that does not exist in table B, and if it exists in table B it has to be that the "date" field in table A is different from the "date" field in the table B.

Table A

id_product,date,description...
3, 2022-07-19
4, 2022-07-20
5, 2022-07-20

Table B

id_product,date,details...
3, 2022-07-20
5, 2022-07-20

The result of the query would be:

id_product,date
3, 2022-07-20
4, 2022-07-20

CodePudding user response：

Try this

 select
   A.* 
from
   TABLE_A A 
   left join
      TABLE_B B 
      on A.ID_PRODUCT = B.ID_PRODUCT 
where
   (
      B.ID_PRODUCT is null 
      or coalesce(A.date, SYSDATE()) <> coalesce(B.date, SYSDATE()) 
   )

If you are not expecting NULL in DATE field, then you can skip coalesce statement.