Unnest an SQL statement

Question

I have this working SQL statement:

select oriseqs.newID from oriseqs WHERE oriseqs.singlets=1 AND 
oriseqs.newID not in (select newID from cleanreport WHERE trash!="")

My question is how to avoid doing one select inside another in this particular case. I mean to rewrite the select statement in a way that there is no nested select.

Here is the relevant schema if needed:

CREATE TABLE cleanreport(newID TEXT, perc TEXT, inicoord INTEGER, endcoord INTEGER, ilen INTEGER, trash TEXT, comments TEXT);
CREATE TABLE oriseqs(newID TEXT, oldID TEXT, rawseq TEXT, singlets BOOLEAN);

Answer 1

There are multiple ways to approach almost any problem in SQL. In your particular problem, the first alternative that springs to mind is to use a left outer join and test for null in a joined column on the second table. However, the query that you have is perfectly reasonable. I see no reason to change it and doubt - at least upon first inspection - that you'll see better performance from an alternative.

Answer 2

select o.newID 
from            oriseqs     o
left outer join cleanreport c on c.newID = o.newID
WHERE o.singlets=1 
AND c.newID is null;

Answer 3

SELECT oriseqs.newID 
FROM oriseqs AS o
LEFT JOIN cleanreport AS cr ON o.newID = cr.newID
WHERE oriseqs.singlets=1 
AND trash != ""
AND cr.newID IS NULL

What are the PKs?