How to select rows with exactly 2 values in a column fast within a table that has 10 million records?

Question

I have a table (TestFI) with the following data for instance

FIID   Email
---------
null [email protected]
1    [email protected]   
null [email protected]    
2    [email protected]    
3    [email protected]    
4    [email protected]    
5    [email protected]    
null [email protected]    
null [email protected]

and I need records that appear exactly twice AND have 1 row with FIID is null and one is not. Such for the data above, only "[email protected] and [email protected]" fit the bill.

I was able to construct a multilevel query like so

    Select
FIID,
Email
from
TestFI
where
Email in
(
    Select
        Email
    from
    (
        Select
                Email
            from
                TestFI
            where
                Email in 
                (
                select
                    Email
                from
                    TestFI
                where
                    FIID is null or FIID is not null
                group by Email
                having 
                    count(Email) = 2
                )
                and
                FIID is null
    )as Temp1
    group by Email
    having count(Email) = 1
)

However, it took nearly 10 minutes to go through 10 million records. Is there a better way to do this? I know I must be doing some dumb things here.

Thanks

Answer 1

I would try this query:

SELECT   EMail, MAX(FFID)
FROM     TestFI
GROUP BY EMail
HAVING   COUNT(*)=2 AND COUNT(FIID)=1

It will return the EMail column, and the non-null value of FFID. The other value of FFID is null.