MySQL join and exclude?

Question

I have two tables, table A one with two columns: IP and ID, and table B with columns: ID and extra information. I want to extract the rows in table B for IPs that are not in table A. So if I have a rows in table A with

id = 1
ip = 000.000.00
id = 2
ip = 111.111.11

and I have rows in table B

id = 1
id = 2

then, given ip = 111.111.11, how can I return row 1 in table B?

Answer 1

The simplest and most easy-to-read way to spell what you're describing is:

SELECT * FROM `B` WHERE `ID` NOT IN (SELECT `ID` FROM `A`)

You should be aware, though, that using a subquery for something like this has historically been slower than doing the same thing with a self-join, because it is easier to optimise the latter, which might look like this:

SELECT
   `B`.*
FROM
   `B`
LEFT JOIN
   `A` ON `A`.`ID` = `B`.`ID`
WHERE
   `A`.`ID` IS NULL

However, technology is improving all the time, and the extent to which this is true (or even whether this is true) depends on the database software you're using.

You should test both approaches then settle on the best balance of readability and performance for your use case.

Answer 2

select b.id, b.* 
from b
left join a on a.id = b.id
where a.id is null

This'll pull all the rows in B that have no matching rows in A. You can add a specific IP into the where clause if you want to try for just that one ip.