how to include Null in "IN Condition"

Question

I want to filter those employees with action_type with value 'Q' ,'R' or it can be null i.e. no action_type. I have used the old SQL syntax since this is the methodology followed in my firm.

I used "Union" in the query to include the null in the output too. But I think there can be a better and full proof way to do this condition is :

 AND ppa.action_type IN ( 'Q', 'R' ) 

SELECT * 
FROM   (SELECT DISTINCT papf.person_number, 
                        name.first_name, 
                        name.last_name, 
                        paam.assignment_number, 
                        py.payroll_name, 
                        (SELECT meaning 
                         FROM   fnd_lookup_values 
                         WHERE  lookup_type LIKE 'ACTION_TYPE' 
                                AND lookup_code = ppa.action_type 
                                AND LANGUAGE = 'US' 
                                AND enabled_flag = 'Y')            ACTION_TYPE, 
                        To_char(PPA.effective_date, 'DD-MON-YYYY') 
                        EFFECTIVE_DATE, 
                        ppa.payroll_action_id                      action_number 
                        , 
                        pcs.consolidation_set_name, 
                        To_char(finc, 'DD-MON-YYYY')               FINC, 
                        pasv.user_status
        FROM   pay_assigned_payrolls_dn papd, 
               pay_payroll_terms pt, 
               pay_pay_relationships_dn pr, 
               pay_all_payrolls_f py, 
               per_all_people_f papf, 
               pay_payroll_actions ppa, 
               pay_consolidation_sets pcs, 
               per_person_names_f name, 
               pay_rel_groups_dn payrel, 
               per_all_assignments_m paam, 
               pay_payroll_rel_actions PPRA, 
               per_assignment_status_types_vl pasv, 
               pay_time_periods ptp, 
               per_legislative_data_groups_vl ldg 
        WHERE  papd.payroll_term_id = pt.payroll_term_id 
               AND pt.payroll_relationship_id = pr.payroll_relationship_id 
               AND papd.payroll_id = py.payroll_id 
               AND pr.person_id = papf.person_id 
               AND ppa.payroll_id(+) = py.payroll_id 
               AND ptp.payroll_id = py.payroll_id 
               AND PPA.payroll_action_id(+) = PPRA.payroll_action_id 
               AND PPRA.payroll_relationship_id = pr.payroll_relationship_id 
               AND pasv.assignment_status_type_id = 
                   paam.assignment_status_type_id 
               AND paam.assignment_type = 'E' 
               --ADDED condition---   
               AND pasv.user_status LIKE '%Payroll Eligible' 
               AND ( finc > ptp.start_date 
                      OR finc IS NULL ) 
               AND ppa.action_type IN ( 'Q', 'R' ) 
               AND ldg.name = Nvl(:P_LEGISLATIVE_GROUP, ldg.name) 
               AND pasv.user_status = Nvl(:P_ASSIGNMENT_STATUS, 
                                      pasv.user_status) 
               AND To_char(Nvl(ppa.effective_date, SYSDATE), 'YYYY-MM-DD') 
                   BETWEEN 
                   To_char(Nvl(:p_from_date, SYSDATE), 'YYYY-MM-DD') AND To_char 
                   ( 
                       Nvl(:p_TO_date, SYSDATE), 'YYYY-MM-DD') 
               -------   
               AND ldg.legislation_code = paam.legislation_code 
               AND ppa.consolidation_set_id(+) = pcs.consolidation_set_id 
               AND pcs.consolidation_set_id = py.consolidation_set_id 
               AND payrel.assignment_id = paam.assignment_id 
               AND payrel.group_type = 'A' 
               AND papf.person_id = name.person_id 
               AND name.name_type = 'GLOBAL' 
               AND payrel.parent_rel_group_id = papd.payroll_term_id 
               AND ppa.earn_time_period_id(+) = ptp.time_period_id 
               AND Nvl(ppa.effective_date, Trunc(SYSDATE)) BETWEEN 
                   payrel.start_date AND payrel.end_date 
               AND Nvl(ppa.effective_date, Trunc(SYSDATE)) BETWEEN 
                   papd.start_date AND papd.end_date 
               AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND 
                                          papf.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN py.effective_start_date AND 
                                          py.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND 
                                          paam.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN name.effective_start_date AND 
                                          name.effective_end_date 
        UNION 
        SELECT DISTINCT papf.person_number, 
                        name.first_name, 
                        name.last_name, 
                        paam.assignment_number, 
                        py.payroll_name, 
                        (SELECT meaning 
                         FROM   fnd_lookup_values 
                         WHERE  lookup_type LIKE 'ACTION_TYPE' 
                                AND lookup_code = ppa.action_type 
                                AND LANGUAGE = 'US' 
                                AND enabled_flag = 'Y')            ACTION_TYPE, 
                        To_char(PPA.effective_date, 'DD-MON-YYYY') 
                        EFFECTIVE_DATE, 
                        ppa.payroll_action_id                      action_number 
                        , 
                        pcs.consolidation_set_name, 
                        To_char(finc, 'DD-MON-YYYY')               FINC, 
                        pasv.user_status
        FROM   pay_assigned_payrolls_dn papd, 
               pay_payroll_terms pt, 
               pay_pay_relationships_dn pr, 
               pay_all_payrolls_f py, 
               per_all_people_f papf, 
               pay_payroll_actions ppa, 
               pay_consolidation_sets pcs, 
               per_person_names_f name, 
               pay_rel_groups_dn payrel, 
               per_all_assignments_m paam, 
               pay_payroll_rel_actions PPRA, 
               per_assignment_status_types_vl pasv, 
               pay_time_periods ptp, 
               per_legislative_data_groups_vl ldg 
        WHERE  papd.payroll_term_id = pt.payroll_term_id 
               AND pt.payroll_relationship_id = pr.payroll_relationship_id 
               AND papd.payroll_id = py.payroll_id 
               AND pr.person_id = papf.person_id 
               AND ppa.payroll_id(+) = py.payroll_id 
               AND ptp.payroll_id = py.payroll_id 
               AND PPA.payroll_action_id(+) = PPRA.payroll_action_id 
               AND PPRA.payroll_relationship_id = pr.payroll_relationship_id 
               AND pasv.assignment_status_type_id = 
                   paam.assignment_status_type_id 
               AND paam.assignment_type = 'E' 
               --ADDED condition---   
               AND pasv.user_status LIKE '%Payroll Eligible' 
               AND ( finc > ptp.start_date 
                      OR finc IS NULL ) 
               AND ppa.action_type IS NULL 
               AND ldg.name = Nvl(:P_LEGISLATIVE_GROUP, ldg.name) 
               AND pasv.user_status = Nvl(:P_ASSIGNMENT_STATUS, 
                                      pasv.user_status) 
               AND To_char(Nvl(ppa.effective_date, SYSDATE), 'YYYY-MM-DD') 
                   BETWEEN 
                   To_char(Nvl(:p_from_date, SYSDATE), 'YYYY-MM-DD') AND To_char 
                   ( 
                       Nvl(:p_TO_date, SYSDATE), 'YYYY-MM-DD') 
               -------   
               AND ldg.legislation_code = paam.legislation_code 
               AND ppa.consolidation_set_id(+) = pcs.consolidation_set_id 
               AND pcs.consolidation_set_id = py.consolidation_set_id 
               AND payrel.assignment_id = paam.assignment_id 
               AND payrel.group_type = 'A' 
               AND papf.person_id = name.person_id 
               AND name.name_type = 'GLOBAL' 
               AND payrel.parent_rel_group_id = papd.payroll_term_id 
               AND ppa.earn_time_period_id(+) = ptp.time_period_id 
               AND Nvl(ppa.effective_date, Trunc(SYSDATE)) BETWEEN 
                   payrel.start_date AND payrel.end_date 
               AND Nvl(ppa.effective_date, Trunc(SYSDATE)) BETWEEN 
                   papd.start_date AND papd.end_date 
               AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND 
                                          papf.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN py.effective_start_date AND 
                                          py.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND 
                                          paam.effective_end_date 
               AND Trunc(SYSDATE) BETWEEN name.effective_start_date AND 
                                          name.effective_end_date)

Answer 1

You can consider using Oracle spesific NVL function (NVL( value1, value2 ) replace value1 with value2 if value1 is null) briefly as

NVL( action_type, 'Q') IN ( 'Q', 'R' )

whenever action_type is null also, NVL( action_type, 'Q') matches with 'Q' which exists in the list within parentheses.

Answer 2

There are two ways:

AND (action_type IN ('Q', 'R') OR action_type IS NULL)

or:

AND COALESCE(action_type, 'Q') IN ('Q', 'R')

Performance may or may not be better with one or the other depending on your particular situation.

Answer 3

How about or?

(ppa.action_type IN ( 'Q', 'R' ) or pa.action_type is null)