The embedded image in this post has sample table structure and data related to my question.
Please refer to it.
I want employee rec to be returned in the output if it satisfies any of the below 3 criterias:
If Employee's salary is > 0
If Employee exist in the EmployeeStatus table with EmpStatus as 'Active'
If Employee does not exist in the EmployeeStatus table
I should not get Employee = 4 in output as Kate's salary is 0, and although exist in the EmployeeStatus table, the EmpStatus is 'Inactive'
I am trying to achieve this using EXISTS clause, but the problem is that when I use EXISTS, only Employee record that also exists in the EmployeeStatus gets returned.
Can you please show me how to achieve this using EXISTS approach, OUTER JOIN Approach, as well as anyother approach that would easily solve this problem.