sql not in 优化问题

问题情境：

　　not in 耗时过长。想用join或exits代替。结果并不明显，这里先记录3种写法，以后探讨速度问题。

sql语句：

                    // not exists
                    sql = @"select c.customerid, c.customername, c.sex, c.birthday, c.CustomerNumber, s.CreateTime, d.description, s.seriesID
                            from customerinfo c, seriestable s, sitecode d
                            where s.customerid = c.customerid
                            and s.createsite = d.siteid
                            and not exists  
                            (select 1 from customer_healthinfo h where h.customerid = c.customerid) 
                            order by s.CreateTime";
                    //not in
                    sql = @"select c.customername, c.sex, c.birthday, c.CustomerNumber, s.CreateTime, d.description, s.seriesID
                            from customerinfo c, seriestable s, sitecode d
                            where s.customerid = c.customerid
                            and s.createsite = d.siteid
                            and c.customerid not in  
                            (select customerid from customer_healthinfo) order by s.CreateTime";
                    //left join
                    sql = @"select customername, sex, birthday, CustomerNumber, tempA.CreateTime, tempA.description, tempA.seriesID
                            from 
                            (select c.customerid, c.customername, c.sex, c.birthday, c.CustomerNumber, s.CreateTime, d.description, s.seriesID
                            from customerinfo c, seriestable s, sitecode d
                            where s.customerid = c.customerid
                            and s.createsite = d.siteid) as tempA left join customer_healthinfo h 
                            on tempA.customerid = h.customerid
                            where h.customerid is null 
                            order by CreateTime";

 

转载于:https://www.cnblogs.com/gaara-zhang/p/10695436.html