Getting rows in left table only if not present in the right table

Question

I need to get only those rows which dont have the corresponding entry in the right table advlabel , so I am doing a full left outer join :

SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,sublabel FROM newparsed
LEFT OUTER JOIN   advlabel ON ((`advlabel`.`seqid` = `newparsed`.`seqid`) AND (`advlabel`.`wordindex` = `newparsed`.`wordindex`))
WHERE sublabel = NULL

So is the above query correct sublabel is a column from the right side table advlabel ?

both tables have primarykey(seqid,wordindex)

Answer 1

Here is an example :

SELECT 
    a.col1
    , a.col2
    , a.col3 
FROM 
   table1 a
   LEFT JOIN table2 b 
       ON 
           (a.pkcol = b.pkcol)
WHERE 
   b.pkcol IS NULL

So in your case, it will most likely be :

SELECT 
    n.seqid
    ,n.wordindex
    ,n.label
    ,n.arg1
    ,n.arg2
    ,n.sublabel 
FROM 
    newparsed n
        LEFT JOIN   advlabel a 
            ON 
                ((a.`seqid` = n.`seqid`) 
            AND 
                (a.`wordindex` = n.`wordindex`))
WHERE 
   a.`seqid` IS NULL

You just have to use LEFT JOIN and select any field (I suggest a primary key) in the right table that is NULL

Answer 2

Use the NOT IN option:

SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,sublabel
FROM   newparsed
where  (seqid, wordindex) NOT IN (SELECT seqid, wordindex FROM advlabel)

Answer 3

SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,sublabel    
FROM   newparsed as np      
WHERE NOT EXISTS  
  (SELECT seqid, wordindex
   FROM   advlabel as a
   WHERE  a.seqid     =np.seqid
   AND    np.wordindex=a.wordindex  )