从PHP MySQL的SELECT语句中获取重复值

Question

My database has two tables: ips, oips

ips.public field contains values: 1, 2, 3, 4, 5, 6, 7, 8

oips.public field contains values: 1, 3, 7

I want to select all values in ips.public that do not appear in oips.public

I'm using the following MySQL query within PHP:

SELECT * FROM ips, oips WHERE ips_ips.public != oips.public

This is returning the following:

1, 1, 2, 2, 2, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 8, 8, 8

As you can see, the values that exist in both tables are only shown twice, whilst everything else is displayed three times (presumably due to this iterating on both tables).

Could anybody please shed some light on how to have this code so that it'd only return the values that are not in both tables (aka: 2, 4, 5, 6, 8)

Thanks!

Answer 1

There are several ways to do this.

I prefer to use NOT EXISTS:

SELECT *
FROM ips i
WHERE NOT EXISTS (
    SELECT 1
    FROM oips o
    WHERE i.ipublic = o.opublic)

Here's NOT IN:

SELECT *
FROM ips
WHERE ipublic NOT IN (SELECT opublic FROM oips)

And here's LEFT JOIN/NULL:

SELECT i.*
FROM ips i
    LEFT JOIN opublic o ON  i.ipublic = o.opublic
WHERE o.opublic IS NULL