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
– chris85, Commented Apr 16, 2015 at 4:01

Robby Cornelissen · Accepted Answer · 2015-04-16 03:38:23Z

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, 2015 at 3:38

Robby Cornelissen

98.6k23 gold badges154 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hitu Bansal Over a year ago

I m getting this error - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

Robby Cornelissen Over a year ago

See stackoverflow.com/questions/3029321/… for solutions

Collectives™ on Stack Overflow

MySql self Join with array in

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related