Is it possible to replace not exists with join in this query?-CodePudding

I have this query

SELECT * 
FROM table1 as t1
WHERE (t1.phone != "" OR t1.sms_phone != "")
  and t1.docType in (1,2,3)
  and not exists (select id from table2 where product_id=1 and doc_id=t1.id);

And I want to replace the part with "not exists" with JOIN, so I tried this way:

SELECT * 
FROM table1 as t1
LEFT OUTER JOIN table2 
  ON table2.doc_id = t1.id 
  AND table2.product_id = 1
  and table2.id IS NULL
WHERE (t1.phone != "" OR t1.sms_phone != "")
  and t1.docType in (1,2,3);

But second query returns much more records..

CodePudding user response：

SELECT t1.*
FROM table1 AS t1 
LEFT JOIN table2 AS t2 ON t2.product_id=1 AND t2.doc_id=t1.id
WHERE (t1.phone != "" OR t1.sms_phone != "") 
  AND t1.docType in (1,2,3) 
  AND t2.doc_id IS NULL

How it works (formally, from user looking point)?

We take table 1 and select rows which matches (t1.phone != "" OR t1.sms_phone != "") AND t1.docType in (1,2,3), because these conditions deals only with table 1. Non-matching rows are ejected.
We take table 2 and select rows which matches t2.product_id=1, because this condition deals only with table 2. Non-matching rows are ejected.
Now we combine these rows using t2.doc_id=t1.id. Non-matching rows from t2 are ejected.
Now according to t2.doc_id IS NULL we select only those rows from t1 which have no paired row from t2.

By the formal execution logic #1 must be performed between #3 and #4, but this does not effect the result (anycase these rows will be ejected), so I put it the most first.

CodePudding user response：

I believe there is an error in the second query and "FROM table1 as t1office_id" part doesn't belong to this query. So, the final query of your would be looking like this

SELECT * 
  FROM table1 as t1 
  LEFT OUTER JOIN table2 
    ON table2.doc_id = t1.id 
   AND table2.product_id = 1 
   and table2.id IS NULL 
 WHERE (t1.phone != "" OR t1.sms_phone != "") and t1.docType in (1,2,3);

When using outer joins, it makes difference what filtering produces depending on where i stands.

Basically your query says "outer join t1 with all lines trom table2 except those .....". This will get you amount of lines equal or more lines to number of those of t1.

What you want thought is to join all the lines and filter them AFTERWARDS. For tht to achieve you have to move filtering clauses to WHERE part of your query. Try this:

SELECT * 
  FROM table1 as t1 
  LEFT OUTER JOIN table2 
    ON table2.doc_id = t1.id 
 WHERE (t1.phone != "" OR t1.sms_phone != "") 
   and t1.docType in (1,2,3)
   AND table2.product_id = 1 
   and table2.id IS NULL;