MySql self Join with array in

Question

I have a table named user, having these values:

 id    email 
  1    [email protected]
  2    [email protected]

Now, I need a query like :

SELECT id, email from user where email in ('[email protected]`,`[email protected]`,`[email protected]`)

As [email protected] doesn't exist in the table, I want the following results:

id    email 
1     [email protected]
2     [email protected]
NULL  [email protected]

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 '[email protected]' AS email
        UNION SELECT '[email protected]'
        UNION SELECT '[email protected]'
    ) 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