With Sql Can You Use A Sub-query In A Where Like Clause?

I'm not even sure how to even phrase this as it sounds weird conceptually, but I'll give it a try. Basically I'm looking for a way to create a query that is essentially a WHERE IN

Solution 1:

You can try

SELECT  u.*FROM    Users u INNERJOIN
        Domains d ON u.UserEmail LIKE'%'+ d.Domain

Or even try

SELECT  u.*FROM    Users u
WHEREEXISTS(SELECT1FROM Domains d WHERE u.UserEmail LIKE'%'+ d.Domain)

Solution 2:

Although

SELECT  u.UserMail
FROM    Users u
WHEREEXISTS(SELECT1FROM Domains d WHERE u.UserEmail LIKE'%'+ d.Domain)

will give what you look for, please realise that like is expensive and that you could shave off a bit of time with (mysql dialect):

SELECT  u.UserMail
FROM    Users u
WHERE   SUBSTRING_INDEX(u.UserMail, '@', -1) IN (SELECT d.Domain FROM Domains)

or even

SELECT  u.UserMail
FROM    Users u
        INNERJOIN Domains d ON SUBSTRING_INDEX(u.UserMail, '@', -1) = d.Domain

(and that you could split e-mail into username and domain fields if this is a common operation in your database)

EDIT: I missed the MS SQL server tag. For that dialect

substring(UserMail, charindex('@', UserMail) + 1, len(UserMail) - charindex('@', UserMail) )

should outperform LIKE (because it will be performed once per row in Users and the you get to straight join, where the like approach will have to be performed for each value in Users on each row in Domains).

P.S. check my formulas for start and length in substring (it was Friday night yesterday).