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?

Change your accepted answer to Marc's answer. – Pacerier Apr 10 '15 at 13:46 — Pacerier, Apr 10 '15 at 13:46

score 32 · Answer 1 · answered Mar 13 '11 at 03:44

32

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.

answered Mar 13 '11 at 03:44

Marc B

356,200
43
426
500

why b.id and b.* at this same time - too ambisious – bensiu Mar 13 '11 at 03:46
3

Because the OP said there's other fields in the table, but didn't specify them. So I'm specifically selecting the fields he included in the question, and put in the `*` to cover the fields he didn't – Marc B Mar 13 '11 at 03:47
1

so obvious, and yet somehow I couldn't figure this one out on my own, thanks a ton! – Eugene Kuzmenko Jul 05 '13 at 12:46

Lightness Races in Orbit · Accepted Answer · 2019-08-28T11:41:30.647

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.

MySQL join and exclude?

2 Answers2

Linked