MySQL merged and excluded?

Question

I have two tables, table A with two columns: IP and ID, and table B with columns: identifier and additional information. I want to extract the rows in table B for IP addresses that are not listed in table A. Therefore, if I have 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 easiest and easiest to read way to write what you are describing is:

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

However, you should be aware that using a subquery for something like this has historically been slower than doing the same with self-join , because it is easier to optimize 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, the technology is constantly being improved, and the extent to which this is true (or even whether it matters) depends on the database software that you use .

You should test both approaches and then choose 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 will pull all the lines from B that do not have matching lines in A. You can add a specific IP address to the where clause if you want to try only one ip.