I have a very large dataset, and I am trying to do a LEFT outer join, but keep losing some of my left table rows because of where I place my WHERE command, similar to the problem and solution here.
Example 1
SELECT *
FROM table1
LEFT JOIN table2
USING (IDvar)
WHERE table2.var IN(val1, val2,..., val100);
This only selects the rows in the first/left table (table1) that have a matching row in the second/right table (table2). The second example is what is likely to work:
Example 2
SELECT *
FROM table1
LEFT JOIN table2
USING (IDvar)
AND (table2.var = val1 OR table2.var = val2);
But, I have like 200 table2.var values that I would like to include, which are sporadic and and non-continuous (can't use syntax like table2.var >= val1).
An example of what I thought should work is to use "AND" and "IN" such as (because I have the values as a comma-separated list):
Example 3
SELECT *
FROM table1
LEFT JOIN table2
USING (IDvar)
AND table2.var IN(val1, val2,..., val100);
So how can I get many many values into an AND command?
I've found a working solution, but it takes way way to long to perform.
Example 4 - Working Example but takes too long
SELECT *
FROM table1
LEFT JOIN (SELECT table2.var WHERE table2.var IN(val1, val2,..., val100)) AS t
USING (IDvar);
Is there any way of optimising this query, it is taking way too long?
