Display empty rows if no records found

Question

I have the following users table:

id, username, email
1,  abcdef,   abcdef@gmail.com
2,  mnopqr,   mnopqr@gmail.com

I would like to get all records in the table where username in ('abcdef', 'ghijkl', 'mnopqr', 'stuvwx') and return null rows if the username is not found. So in the following table I am expecting the following result (in the same order that the username appear in my IN clause:

id, username, email
1,  abcdef,   abcdef@gmail.com
null,  null,   null -- null values for username ghijkl
2,  mnopqr,   mnopqr@gmail.com
null,  null,   null -- null values for username stuvwx

How can I achieve that?

Could you please define the dataset to address this problem statement. Are there any nulls in username against an email? — ishant kaushik, Nov 18 '21 at 01:55
Is there any other table where you have username as 'ghijkl' and 'stuvwx'? Because in users table I can see null. So how are you relating some value to null. Pardon me ,trying to understand the problem. Thanks in advance — ishant kaushik, Nov 18 '21 at 02:10
There are is no null in users table, I just want the query to return null rows if the username specified in the IN clause does not exist in users table. — user765368, Nov 18 '21 at 02:12
in the exact order which you displayed in output or null values can come at last of resultset? — ishant kaushik, Nov 18 '21 at 02:16
https://stackoverflow.com/questions/985295/can-you-define-literal-tables-in-sql may help. — Matt Raines, Nov 18 '21 at 10:57

score 0 · Answer 1 · answered Nov 18 '21 at 01:09

0

CREATE TEMPORARY TABLE usernames (username VARCHAR(10) PRIMARY KEY);

INSERT INTO usernames VALUES ('abcdef'), ('ghijkl'), ('mnopqr'), ('stuvwx');

SELECT tu.username, u.email
FROM usernames AS tu
LEFT OUTER JOIN users AS u USING (username);

answered Nov 18 '21 at 01:09

Bill Karwin

538,548
86
673
828

Display empty rows if no records found

1 Answers1