CODEWITHSUNDEEP
mysql
Uchsun
Uchsun

Reputation: 361

Sub Query Not IN With Left Join Alternative?

I have Query Like This

SELECT  f.ACCOUNT_ID,
        f.TGL,
        p.ACCOUNT_EMAILADDRESS


     FROM distributor_kokola.forecast f
      inner join distributor_kokola.push_distributor p on p.ACCOUNT_ID = f.ACCOUNT_ID
      where f.ACCOUNT_ID NOT IN(

                 select ACCOUNT_ID
                        from distributor_kokola.forecast
                         where DATE_FORMAT(TGL, "%Y-%m") = DATE_FORMAT(CURDATE(), "%Y-%m")  
                         group by ACCOUNT_ID


    )

      group by f.ACCOUNT_ID;

That Sub Query work but To Slow so I change it with Left Join, it work faster

SELECT  f.ACCOUNT_ID,
        f.TGL,
        p.ACCOUNT_EMAILADDRESS


     FROM distributor_kokola.forecast f
       left  join(

                 select ACCOUNT_ID
                        from distributor_kokola.forecast
                         where DATE_FORMAT(TGL, "%Y-%m") = DATE_FORMAT(CURDATE(), "%Y-%m")  
                         group by ACCOUNT_ID


    )subb on subb.ACCOUNT_ID = f.ACCOUNT_ID

       inner join distributor_kokola.push_distributor p on p.ACCOUNT_ID = f.ACCOUNT_ID
      group by f.ACCOUNT_ID;

But, My issue is Still Contain wrong Result,

With Not IN, query 1 select where NOT IN query 2.

How can I get like NOT IN query with left join.

Can Anyone Help Me?

thanks.

Upvotes: 0

Views: 77

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30775

You have to add a WHERE clause to filter by the results of your LEFT JOIN. If you add an appropriate WHERE clause WHERE subb.ACCOUNT_ID IS NULL, it should work as expected (since you used a GROUP BY in your subquery, there's no danger of duplicate rows):

SELECT  f.ACCOUNT_ID,
        f.TGL,
        p.ACCOUNT_EMAILADDRESS
     FROM distributor_kokola.forecast f
       left  join(
                 select ACCOUNT_ID
                        from distributor_kokola.forecast
                         where DATE_FORMAT(TGL, "%Y-%m") = DATE_FORMAT(CURDATE(), "%Y-%m")  
                         group by ACCOUNT_ID
    )  subb on subb.ACCOUNT_ID = f.ACCOUNT_ID
       inner join distributor_kokola.push_distributor p on p.ACCOUNT_ID = f.ACCOUNT_ID
      WHERE sub.ACCOUNT_ID IS NULL
      group by f.ACCOUNT_ID;

Update

The goal of our LEFT JOIN is to find all rows in our forecast table that don't have a matching row in the subquery. Therefore, we need a WHERE clause that removes all rows with a matching row - WHERE sub.ACCOUNT_ID IS NULL fits quite nicely.

SO user @quassnoi has written a wonderful comparison of different methods to achieve this goal.

Upvotes: 1

Related Questions