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

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!

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

"only return the values that are not in both tables" is not the same as "all values in ips.public that do not appear in oips.public".

The query below will show you all public values that are not in both tables.

select public from (
    select public from ips
    union all
    select public from oips
) t1 group by public
having count(*) = 1

If public can be duplicated within each table use select distinct for the inner select statements.