CODEWITHSUNDEEP
mysql
wutzebaer
wutzebaer

Reputation: 14865

Difference between SELECT EXISTS and EXISTS

I had an Query like this

    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?

Here the complete query, the change was on line 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)
        ))

Upvotes: 0

Views: 260

Answers (1)

Rahul
Rahul

Reputation: 77876

Have you checked the Documentation; actual syntax is

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

Per your edit in post, you are actually doing below which is not what you have shown in first query

WHERE 
    NOT EXISTS  (  subquery )
    AND 
    EXISTS ( subquery )

See @siride comments, only difference I can say is in case of AND (EXISTS (subquery)) if subquery returns rows then it's evaluated as AND TRUE whereas in case of AND (SELECT EXISTS ( subquery )) it would evaluate as AND SELECT TRUE ... BUT in either case the EXISTS logic should work fine and your query should return same expected result set but per your post you say that the first version AND (SELECT EXISTS ( subquery )) doesn't fetch records ... which I am really skeptic about.

Upvotes: 1

Related Questions