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..
2
Answers
How it works (formally, from user looking point)?
(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.t2.product_id=1
, because this condition deals only with table 2. Non-matching rows are ejected.t2.doc_id=t1.id
. Non-matching rows from t2 are ejected.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.
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
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: