mysql - SELECT EXISTS 和 EXISTS 之间的区别

Question

我有一个这样的查询

    SELECT ... FROM ... WHERE (SELECT EXISTS (SELECT...))

which did not return anything then i changed it to 

    SELECT ... FROM ... WHERE (EXISTS (SELECT...))

and i got my results, can anyone explain the difference?

这里是完整的查询，更改位于第 40 行:

SELECT 
    user_element_status.user_id, 
    user_element_status.element_id, 
    notifications.id 
FROM 
    notifications 
    INNER JOIN notification_element ON  (notification_element.notification_id=notifications.id) 
    INNER JOIN user_element_status  ON  ( 
                                        user_element_status.element_id=notification_element.element_id 
                                    AND 
                                        case
                                            when notifications.notstatus=0 then notifications.notification_status LIKE CONCAT('%',user_element_status.`status`,'%')
                                            when notifications.notstatus=1 then notifications.notification_status NOT LIKE CONCAT('%',user_element_status.`status`,'%')
                                        End 
                                    AND user_element_status.`status` != 'not_required'
                                    AND 
                                        DATE_ADD(
                                        case 
                                            when notifications.notification_trigger='assigned' then user_element_status.assigned 
                                            when notifications.notification_trigger='first_launch' then user_element_status.firstlaunch 
                                            when notifications.notification_trigger='completed' then user_element_status.completedate 
                                            when notifications.notification_trigger='due' then user_element_status.duedate
                                            when notifications.notification_trigger='credited' then user_element_status.creditcompletedate
                                        End 
                                        , INTERVAL triggerdelay DAY)
                                        - CURRENT_TIMESTAMP < 0 
                                    )
    INNER JOIN users ON (users.id=user_element_status.user_id AND users.is_active=1)


WHERE 
    NOT EXISTS  ( 
                    SELECT * FROM alreadysent 
                    WHERE 
                        alreadysent.user_id=user_element_status.user_id AND 
                        alreadysent.element_id=notification_element.element_id AND 
                        alreadysent.notification_id=notification_element.notification_id
                )
    AND 
    (EXISTS (
        SELECT
            user_group.user_id,
            element_group.element_id
        FROM user_group
            user_group
            INNER JOIN users ON (users.id=user_group.user_id)
            LEFT JOIN element_group ON (element_group.group_id=user_group.group_id)
            LEFT JOIN elements ON (elements.group_id=user_group.group_id)
            LEFT JOIN element_localization ON ((element_localization.element_id=element_group.element_id OR element_localization.element_id=elements.id) AND users.country_id=element_localization.country_id)
            LEFT JOIN element_arealocalisation ON (element_arealocalisation.element_id = element_group.element_id OR element_arealocalisation.element_id=elements.id)
            LEFT JOIN area_country ON (element_arealocalisation.area_id = area_country.area_id AND area_country.country_id=users.country_id)
        WHERE 
            users.is_active=1 AND
            user_group.user_id=user_element_status.user_id AND
            (element_group.element_id=user_element_status.element_id OR elements.id=user_element_status.element_id) AND
            (element_localization.country_id IS NOT NULL OR area_country.country_id IS NOT NULL)
        ))

Answer 1

您检查过Documentation吗？ ;实际语法是

SELECT .. FROM .. WHERE [NOT]EXISTS (Subquery);

根据您在帖子中的编辑，您实际上正在执行以下操作，这不是您在第一个查询中显示的内容

WHERE 
    NOT EXISTS  (  subquery )
    AND 
    EXISTS ( subquery )

参见@siride评论，我能说的唯一区别是在AND (EXISTS (subquery))的情况下，如果子查询返回行，那么它被评估为AND TRUE，而在在 AND (SELECT EXISTS ( subquery )) 的情况下，它将计算为 AND SELECT TRUE ... BUT 在任何一种情况下 EXISTS 逻辑应该正常工作，您的查询应该返回相同的预期结果集，但根据您的帖子，您说第一个版本 AND (SELECT EXISTS ( subquery )) 不获取记录...我真的很怀疑。