MySql self Join with array in

Question

I have a table named user, having these values:

 id    email 
  1    abc@abc.com
  2    test@abc.com

Now, I need a query like :

SELECT id, email from user where email in ('abc@abc.com`,`test@abc.com`,`xyz@aa.com`)

As xyz@aa.com doesn't exist in the table, I want the following results:

id    email 
1     abc@abc.com
2     test@abc.com
NULL  xyz@aa.com

Any idea how to do this in MySql?

I know of no way a SQL statement can be injected when it isn't taking in user input. If those emails are taken from user input they should be separated out. I'd be interested to know how this could be injected as it currently stands, @TimBiegeleisen. — chris85, Apr 16 '15 at 04:01

score 3 · Accepted Answer · answered Apr 16 '15 at 03:38

3

Here's one way:

SELECT user.id, user.email
FROM user
RIGHT JOIN (
    SELECT 'abc@abc.com' AS email
        UNION SELECT 'test@abc.com'
        UNION SELECT 'xyz@aa.com'
    ) AS tmp
ON user.email = tmp.email;

answered Apr 16 '15 at 03:38

Robby Cornelissen

91,784
22
134
156

I m getting this error - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '=' – Hitu Bansal Apr 16 '15 at 03:57
See http://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql for solutions – Robby Cornelissen Apr 16 '15 at 04:02

MySql self Join with array in

1 Answers1