Sql Queries To find unmatched data in many to many relationship

Question

I need help in the sql queries.

I've two table Person_Table and PersonAlias_Table. In the first table person_id and uniqe_id are primary key and in the Alias Table also the person_id and uniqe_id along with activity_code are primary. I want to find the data which are in Alias table but not in person table.

Mariappan Subramanian · Accepted Answer · 2013-01-02T10:10:41.993

2

select *
  from PersonAlias_Table PA
       left outer join Person_Table P on PA.person_id = P.person_id 
       and PA.uniqe_id = P.uniqe_id
 where P.uniqe_id is null

Try this

edited Jan 02 '13 at 10:10

answered Jan 02 '13 at 09:24

Mariappan Subramanian

9,527
8
32
33

Hi I understood the idea. May I know why we need to have the last where clause (where P.uniqe_id is null). I'm not able to figure it out. – vvekselva Jan 02 '13 at 10:08
yeah the thing is left outer join will give you null values on right table for unmatched records. To filter out that we are using that null condition. – Mariappan Subramanian Jan 02 '13 at 10:11

score 0 · Answer 2 · edited May 23 '17 at 10:09

The best way of doing this depends on your DBMS, you can either use NOT EXISTS or LEFT JOIN/IS NULL

NOT EXISTS

SELECT  *
FROM    PersonAlias_Table pat
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    Person_Table pt
            WHERE   pt.Person_ID = pat.Person_ID
            AND     pt.Uniqe_ID = pat.Uniqe_ID
        )

LEFT JOIN/IS NULL

SELECT  *
FROM    PersonAlias_Table pat
        LEFT JOIN Person_Table pt
            ON pt.Person_ID = pat.Person_ID
            AND pt.Uniqe_ID = pat.Uniqe_ID
WHERE    pt.Person_ID IS NULL

According to this answer in MySQL NOT EXISTS is a little bit less efficient, In SQL Server LEFT JOIN / IS NULL is less efficient, in PostgreSQL and Oracle both methods are the same, so it is really personal preference.

Sql Queries To find unmatched data in many to many relationship

2 Answers2