从带条件的表2中不存在的表1中选择* [英] Select * from table1 that does not exist in table2 with conditional

问题描述

我有2张桌子.一个是一张桌子，上面有可以学习的东西.有一个JID描述了每种类型的行，并且对于每一行都是唯一的.第二张表是已经学习过的东西的日志(JID)，以及学习它的人的用户ID.我目前正在使用它来选择JID的所有数据，但只能选择用户根据用户ID获悉的数据.

I have 2 tables. One is a table with things that can be learned. There is a JID that desribes each kind of row, and is unique to each row. The second table is a log of things that have been learned (the JID) and also the userid for the person that learned it. I am currently using this to select all of the data for the JID, but only the ones the user has learned based on userid.

SELECT * 
FROM tablelist1
LEFT JOIN tablelog2 ON (tablelist1.JID = tablelog2.JID) 
                       AND tablelog2.UID = 'php var'
WHERE tablelog2.JID IS NOT NULL

我现在需要选择要学习的东西行，但只选择用户ID尚未学习的东西.我显然对此很陌生，请多多包涵. :)我尝试使用IS NULL，但看起来似乎可行，但它给出了重复的JID，其中一个为NULL，一个为正确.

I now need to select the rows of things to learn, but only the things the userid has NOT already learned. I am obviously very new to this, bear with me. :) I tried using IS NULL, but while it seems it works, it gives duplicate JID's one being NULL, one being correct.

推荐答案

使用LEFT JOIN/IS NULL:

Using LEFT JOIN/IS NULL:

   SELECT t.*
     FROM TABLE_LIST t
LEFT JOIN TABLE_LOG tl ON tl.jid = t.jid
    WHERE tl.jid IS NULL

使用NOT IN:

SELECT t.*
  FROM TABLE_LIST t
 WHERE t.jid NOT IN (SELECT tl.jid
                       FROM TABLE_LOG tl
                   GROUP BY tl.jid)

使用不存在:

SELECT t.*
  FROM TABLE_LIST t
 WHERE NOT EXISTS(SELECT NULL
                    FROM TABLE_LOG tl
                   WHERE tl.jid = t.jid)

仅供参考
在MySQL中，LEFT JOIN/IS NULL和NOT IN是等效的-它们将执行相同的操作，而NOT EXISTS的执行速度较慢/效率较低.有关更多详细信息:

FYI
LEFT JOIN/IS NULL and NOT IN are equivalent in MySQL - they will perform the same, while NOT EXISTS is slower/less efficient. For more details: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

这篇关于从带条件的表2中不存在的表1中选择*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！