SQL select item from one table that is not in another

Question

I have a php page with a variable $lid, and a user with vairable $uid... I need to select some data from 2 tables to fill out the page.

        Table 1                          Table 2
¦----------¦----------¦    ¦----------¦----------¦----------¦
¦    qid   ¦    lid   ¦    ¦   owner  ¦   qid    ¦timestamp ¦
¦----------¦----------¦    ¦----------¦----------¦----------¦

I need to write an SQL statement that gets everything from table 2 where the owner = $uid if the qid is not already listed in table1 with the current pages' $lid.

I tried

SELECT * FROM table_two WHERE qid != (SELECT qid FROM table_one WHERE lid = " . $lid .") AND owner = " . $uid . ";

But had no joy

Any ideas?

Solution 1

This would work:

SELECT * 
FROM table_two 
WHERE qid not in (
    SELECT qid 
    FROM table_one 
    WHERE lid = " . $lid .") 

This will probably perform better:

SELECT T2.* 
FROM table_two t2
LEFT OUTER JOIN table_one T1 ON T2.QID = T1.QID 
    AND T1.LID = " . $lid ."
WHERE T1.qid IS NULL

Solution 2

You should use LEFT JOIN for best perofrmance:

SELECT a.*
FROM table_two a
    LEFT JOIN table_one b
        ON a.qid = b.qid AND
           b.lid = " . $lid ."
WHERE b.qid IS NULL;

See Visual Explanation Of Joins

Solution 3

SELECT * FROM table_two WHERE qid not in 
    (SELECT qid FROM table_one WHERE lid = " . $lid .")

Solution 4

SELECT * FROM table_two WHERE qid NOT IN (SELECT qid FROM table_one WHERE lid = " . $lid .")

Use NOT IN clause. This will give you the Id's that are not in your subquery.

Edit You could also use Left-Join, In MSSQL is less efficcient, but this is MySQL (I didn't notice that), so they run equally. You can see it here

View more solutions

Comments

[Fluffeh]

That is the most efficient query from the 4 answers. +1

[Gonzalo.-]

no, is not. blog.sqlauthority.com/2008/04/22/…

[Omesh]

thanks! I think we are talking about MySQL server. I think that in most of the cases LEFT JOIN performs better than sub-query. Anyways it's always best practice to use EXPLAIN for every query.

[Gonzalo.-]

this probably wrong. Considering he is returning a different value that the one's using on the where

[Gonzalo.-]

you're right, mysql, sorry about that. In that case, they both run equally :P explainextended.com/2009/09/18/…

[Gonzalo.-]

Also, considering it's good, is less efficcient that another 4 options explainextended.com/2009/09/18/…

[Chris Headleand]

Hi... This query doesnt return the qid for some reason. Is there a way it can be edited to return that as well?

[Omesh]

you should use a.qid column in SELECT clause as our WHERE condition is 'b.qid IS NULL'