PHPMYADMIN: Access field in EXISTS clause-CodePudding

I am sure it's just a typo, but how to write the following query correctly in PHPMyAdmin?

SELECT DISTINCT `email_address` as tmp1 
FROM `already_customer_checks` 
WHERE `is_customer` = 0 
AND NOT EXISTS (
                    SELECT * 
                    FROM `already_customer_checks` 
                    WHERE `email_address` = tmp1 
                    AND `is_customer` = 1
                )

Error: #1054 - Unknown table field 'tmp1' in where clause

Background: I want to get all e-mail addresses which exist with 'is_customer' = 0 and do not have another existance in the table with 'is_customer' = 1.

Thank you very much in advance!

CodePudding user response：

To do it with a subquery you need to put the alias tmp1 on the table, not on the column. And then:

SELECT DISTINCT `email_address`
FROM `already_customer_checks` as tmp1 
WHERE `is_customer` = 0 
AND NOT EXISTS (
    SELECT * 
    FROM `already_customer_checks` 
    WHERE `email_address` = tmp1.`email_address`
    AND `is_customer` = 1
)

You might also consider the comment proposed by @kmoser, which could be more efficient, if less clear. According to the MySQL docs:

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.

But if you use that SQL proposed by @kmoser, you probably don't want to alias the email_address column with tmp1.