LEFT JOIN vs INNER JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

LEFT JOIN vs INNER JOIN

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-02 : 11:33:53

I recently changed the 3 INNER JOINS in the query below to LEFT JOINS as mentioned. The query with the inner join would take less than a minute to execute. But when I changed it to LEFT JOIN , it took more than 30 minutes. The table CPRAPEXT with which all tables are joined has only about 300 rows, but the other tables are huge.

I checked the execution plans. The first query keeps joining one table with the resultset, and then the next table with the resultset and so on. But in the Left join query, the PPBEN (A) and PPBENTYP are joined separately and then joined with the resultset. Both these tables are huge, which is what is killing the query I think.

Any reason for this behavior?

SELECT   b.policy_number                                                                                         AS policy_number, 
         @SEQ_NUM, 
         b.product_code                                                                                          AS product_code, 
         '0' + Cast(benseq1.issue_age AS CHAR(2))                                                                AS issue_age, 
         benseq1.sex_code, 
         b.company_code, 
         Cast(c.account_number AS CHAR(15))                                                                      AS account_number, 
         b.issue_state, 
         b.res_state, 
         Replicate('0',2 - Len(Rtrim(Cast(b.billing_mode AS CHAR(2))))) + Rtrim(Cast(b.billing_mode AS CHAR(2))) AS payment_mode, 
         b.contract_code, 
         b.contract_reason, 
         b.billing_code, 
         b.billing_reason, 
         Cast(CASE 
                WHEN (d.follow_up_code IN ('WAV1','WAV2')) 
                THEN 'Y' 
                ELSE ' ' 
              END AS CHAR(4)) AS final_foll_up_cd, 
         benseq1.status_code                                                                                     AS final_status_code, 
         benseq1.status_reason                                                                                   AS final_status_reason, 
         Cast(Isnull(e.agent,'') AS CHAR(10))                                                                    AS serv_agent, 
         Cast(Isnull(Substring(f.agent,3,5),'') AS CHAR(5))                                                      AS inforce_office, 
         CASE Ascii(h.cancel_reason) 
           WHEN 0 
           THEN '' 
           ELSE h.cancel_reason 
         END                          AS cancel_reason, -- CANCEL REASON column stores Invalid Values in the database this is handled using the ASCII Value. 
         Cast(' ' AS CHAR(200))                                                                                  AS lp_filler, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.benefit_type 
                      ELSE '' 
                    END),'') AS benefit_type, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.parent_benefit_seq 
                      ELSE '0' 
                    END),'0') AS benefit_seq1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.plan_code 
                      ELSE ' ' 
                    END),'') AS plan_code1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.value_per_unit 
                      ELSE '0' 
                    END),'0') AS value_per_unit1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.number_of_units 
                      ELSE '0' 
                    END),'0') AS number_of_units1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                           AND a.benefit_type = 'SL' 
                      THEN a.benefit_seq 
                      ELSE '000' 
                    END),'000') AS sub_seq1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.issue_date 
                      ELSE '0' 
                    END),'0') AS issue_date1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_flat_amount 
                      ELSE '0' 
                    END),'0') AS sl_flat_amount1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_flat_cease_date 
                      ELSE '0' 
                    END),'0') AS sl_flat_cease_date1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_table_code 
                      ELSE ' ' 
                    END),'0') AS sl_table_code1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN (bt.sl_percent * 100) 
                      ELSE '0' 
                    END),'0') AS sl_2nd_percent1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_pct_cease_date 
                      ELSE '0' 
                    END),'0') AS sl_pct_cease_date1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_rate_up_age 
                      ELSE '0' 
                    END),'0') AS sl_rate_up_age1 
FROM     cprapext AS cp 
         INNER JOIN ppolc AS b WITH (NOLOCK) 
           ON b.polc_key0 = Cast(cp.company_code + Cast(cp.policy_number AS CHAR(12)) AS BINARY(14)) 
         INNER JOIN ppben AS a WITH (NOLOCK) 
           ON b.policy_number = a.policy_number 
              AND b.company_code = a.company_code 
         INNER JOIN ppbentyp AS bt WITH (NOLOCK) 
           ON bt.pben_key0 = a.pben_key0 
         INNER JOIN ppben AS benseq1 WITH (NOLOCK) 
           ON (Cast(b.company_code + Cast(b.policy_number AS CHAR(12)) + '01' AS BINARY(16))) = benseq1.pben_key0 
         LEFT JOIN pacct c WITH (NOLOCK) 
           ON (Cast(b.company_code + Cast(b.policy_number AS CHAR(12)) AS BINARY(16)) = c.acct_key0) 
         LEFT JOIN pfdat d WITH (NOLOCK) 
           ON (b.policy_number = d.pol_agt_clm_number 
               AND d.status_code = 'A' 
               AND d.key_type = 'P' 
               AND d.follow_up_code IN ('WAV1','WAV2') 
               AND a.company_code = d.company_code) 
         LEFT JOIN (SELECT Substring(comc_key0,1,14) AS comc_key0, 
                           agent, 
                           x, 
                           split_eff_date 
                    FROM   pcomcagnts WITH (NOLOCK) 
                    WHERE  (x = 1)) e 
           ON (b.polc_key0 = e.comc_key0 
               AND b.issue_date = e.split_eff_date) 
         LEFT JOIN (SELECT Substring(comc_key0,1,14) AS comc_key0, 
                           agent, 
                           x, 
                           split_eff_date 
                    FROM   pcomcagnts WITH (NOLOCK) 
                    WHERE  (x = 2)) f 
           ON (b.polc_key0 = f.comc_key0 
               AND b.issue_date = f.split_eff_date) 
         INNER JOIN ppolm AS h WITH (NOLOCK) 
           ON (Cast(b.company_code + Cast(b.policy_number AS CHAR(12)) + '000' + '012' AS BINARY(20)) = h.polm_key0) 
GROUP BY b.policy_number, 
         b.company_code, 
         benseq1.issue_age, 
         benseq1.sex_code, 
         b.product_code, 
         c.account_number, 
         b.issue_state, 
         b.res_state, 
         b.billing_mode, 
         b.contract_code, 
         b.contract_reason, 
         b.billing_code, 
         b.billing_reason, 
         d.follow_up_code, 
         benseq1.status_code, 
         benseq1.status_reason, 
         e.agent, 
         f.agent, 
         h.cancel_reason

SELECT   b.policy_number                                                                                         AS policy_number, 
         @SEQ_NUM, 
         b.product_code                                                                                          AS product_code, 
         '0' + Cast(benseq1.issue_age AS CHAR(2))                                                                AS issue_age, 
         benseq1.sex_code, 
         b.company_code, 
         Cast(c.account_number AS CHAR(15))                                                                      AS account_number, 
         b.issue_state, 
         b.res_state, 
         Replicate('0',2 - Len(Rtrim(Cast(b.billing_mode AS CHAR(2))))) + Rtrim(Cast(b.billing_mode AS CHAR(2))) AS payment_mode, 
         b.contract_code, 
         b.contract_reason, 
         b.billing_code, 
         b.billing_reason, 
         Cast(CASE 
                WHEN (d.follow_up_code IN ('WAV1','WAV2')) 
                THEN 'Y' 
                ELSE ' ' 
              END AS CHAR(4)) AS final_foll_up_cd, 
         benseq1.status_code                                                                                     AS final_status_code, 
         benseq1.status_reason                                                                                   AS final_status_reason, 
         Cast(Isnull(e.agent,'') AS CHAR(10))                                                                    AS serv_agent, 
         Cast(Isnull(Substring(f.agent,3,5),'') AS CHAR(5))                                                      AS inforce_office, 
         CASE Ascii(h.cancel_reason) 
           WHEN 0 
           THEN '' 
           ELSE h.cancel_reason 
         END                          AS cancel_reason, -- CANCEL REASON column stores Invalid Values in the database this is handled using the ASCII Value. 
         Cast(' ' AS CHAR(200))                                                                                  AS lp_filler, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.benefit_type 
                      ELSE '' 
                    END),'') AS benefit_type, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.parent_benefit_seq 
                      ELSE '0' 
                    END),'0') AS benefit_seq1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.plan_code 
                      ELSE ' ' 
                    END),'') AS plan_code1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.value_per_unit 
                      ELSE '0' 
                    END),'0') AS value_per_unit1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.number_of_units 
                      ELSE '0' 
                    END),'0') AS number_of_units1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                           AND a.benefit_type = 'SL' 
                      THEN a.benefit_seq 
                      ELSE '000' 
                    END),'000') AS sub_seq1, 
         Isnull(Max(CASE 
                      WHEN a.benefit_seq = @SEQ_NUM 
                      THEN a.issue_date 
                      ELSE '0' 
                    END),'0') AS issue_date1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_flat_amount 
                      ELSE '0' 
                    END),'0') AS sl_flat_amount1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_flat_cease_date 
                      ELSE '0' 
                    END),'0') AS sl_flat_cease_date1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_table_code 
                      ELSE ' ' 
                    END),'0') AS sl_table_code1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN (bt.sl_percent * 100) 
                      ELSE '0' 
                    END),'0') AS sl_2nd_percent1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_pct_cease_date 
                      ELSE '0' 
                    END),'0') AS sl_pct_cease_date1, 
         Isnull(Max(CASE 
                      WHEN bt.benefit_seq = @SEQ_NUM 
                      THEN bt.sl_rate_up_age 
                      ELSE '0' 
                    END),'0') AS sl_rate_up_age1 
FROM     cprapext AS cp 
         INNER JOIN ppolc AS b WITH (NOLOCK) 
           ON b.polc_key0 = Cast(cp.company_code + Cast(cp.policy_number AS CHAR(12)) AS BINARY(14)) 
         LEFT JOINppben AS a WITH (NOLOCK) 
           ON b.policy_number = a.policy_number 
              AND b.company_code = a.company_code 
         LEFT JOIN ppbentyp AS bt WITH (NOLOCK) 
           ON bt.pben_key0 = a.pben_key0 
         LEFT JOIN ppben AS benseq1 WITH (NOLOCK) 
           ON (Cast(b.company_code + Cast(b.policy_number AS CHAR(12)) + '01' AS BINARY(16))) = benseq1.pben_key0 
         LEFT JOIN pacct c WITH (NOLOCK) 
           ON (Cast(b.company_code + Cast(b.policy_number AS CHAR(12)) AS BINARY(16)) = c.acct_key0) 
         LEFT JOIN pfdat d WITH (NOLOCK) 
           ON (b.policy_number = d.pol_agt_clm_number 
               AND d.status_code = 'A' 
               AND d.key_type = 'P' 
               AND d.follow_up_code IN ('WAV1','WAV2') 
               AND a.company_code = d.company_code) 
         LEFT JOIN (SELECT Substring(comc_key0,1,14) AS comc_key0, 
                           agent, 
                           x, 
                           split_eff_date 
                    FROM   pcomcagnts WITH (NOLOCK) 
                    WHERE  (x = 1)) e 
           ON (b.polc_key0 = e.comc_key0 
               AND b.issue_date = e.split_eff_date) 
         LEFT JOIN (SELECT Substring(comc_key0,1,14) AS comc_key0, 
                           agent, 
                           x, 
                           split_eff_date 
                    FROM   pcomcagnts WITH (NOLOCK) 
                    WHERE  (x = 2)) f 
           ON (b.polc_key0 = f.comc_key0 
               AND b.issue_date = f.split_eff_date) 
         INNER JOIN ppolm AS h WITH (NOLOCK) 
           ON (Cast(b.company_code + Cast(b.policy_number AS CHAR(12)) + '000' + '012' AS BINARY(20)) = h.polm_key0) 
GROUP BY b.policy_number, 
         b.company_code, 
         benseq1.issue_age, 
         benseq1.sex_code, 
         b.product_code, 
         c.account_number, 
         b.issue_state, 
         b.res_state, 
         b.billing_mode, 
         b.contract_code, 
         b.contract_reason, 
         b.billing_code, 
         b.billing_reason, 
         d.follow_up_code, 
         benseq1.status_code, 
         benseq1.status_reason, 
         e.agent, 
         f.agent, 
         h.cancel_reason

EDIT : formatted the code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 12:25:13

replace


...
INNER JOIN ppolc AS b WITH (NOLOCK) 
           ON b.polc_key0 = Cast(cp.company_code + Cast(cp.policy_number AS CHAR(12)) AS BINARY(14))
LEFT JOINppben AS a WITH (NOLOCK) 
           ON b.policy_number = a.policy_number 
              AND b.company_code = a.company_code 
         LEFT JOIN ppbentyp AS bt WITH (NOLOCK) 
           ON bt.pben_key0 = a.pben_key0 
         LEFT JOIN ppben AS benseq1 WITH (NOLOCK) 
           ON (Cast(b.company_code + Cast(b.policy_number AS CHAR(12)) + '01' AS BINARY(16))) = benseq1.pben_key0


...
INNER JOIN ppolc AS b WITH (NOLOCK) 
           ON b.polc_key0 = Cast(cp.company_code + Cast(cp.policy_number AS CHAR(12)) AS BINARY(14))
LEFT JOIN
(SELECT *
FROM ppben AS a WITH (NOLOCK) 
         INNER JOIN ppbentyp AS bt WITH (NOLOCK) 
           ON bt.pben_key0 = a.pben_key0 
         INNER JOIN ppben AS benseq1 WITH (NOLOCK) 
           ON (Cast(a.company_code + Cast(a.policy_number AS CHAR(12)) + '01' AS BINARY(16))) = benseq1.pben_key0 ) tmp
 ON b.policy_number = tmp.policy_number 
              AND b.company_code = tmp.company_code

and see if there's any difference

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-02 : 15:11:49

Thanks Visakh, but it is still taking the same amount of time. The plan has changed according to the query, creating the derived table first, but no improvement in timings

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources