Finding ids that have not yet been assigned in another table

I have a one-to-one relation between my webscrapers and a list of proxies. Now I’d like to know what proxies are still available to be assigned to a webscraper.

bots

enter image description here

proxies

enter image description here

I basically need the ids of proxies that have not yet been assigned to a bots row. I’ve been quite rusty with my SQL but I was thinking this would just be a simple join but performing the following join just resulted me into all rows being spat back at me;`

SELECT DISTINCT proxies.* FROM proxies JOIN bots ON bots.proxy_id <> proxies.id;

Answer

An alternative to subqueries is an outer join, and then search for cases where there is no match, i.e the outer join produced NULL in the matching table.

SELECT p.*
FROM proxies AS p
LEFT OUTER JOIN bot AS b ON b.proxy_id = p.id
WHERE b.proxy_id IS NULL;

You can try both this solution and the subquery solution given by @GordonLinoff on this thread, and see which one performs better against your data.

Leave a Reply

Your email address will not be published. Required fields are marked *