How to get non existing value supplied in ` where in statement` as custom provided value in mysql?

Question

I have query !

Select a.EmployeeID,a.FirstName,a.LastName,b.DepartmentName
from Employee as a
Inner Join Department as b
On a.DepartmentID=b.DepartmentID
Where a.EmployeeID in (1,2,3,4,5);

Which gives me this result.

╔════════════╦═══════════╦══════════╦════════════════╗
║ EmployeeID ║ FirstName ║ LastName ║ DepartmentName ║
╠════════════╬═══════════╬══════════╬════════════════╣
║ 1          ║ Shahab    ║ Haidar   ║ IT             ║
╠════════════╬═══════════╬══════════╬════════════════╣
║ 2          ║ Aditya    ║ Asija    ║ HR             ║
╠════════════╬═══════════╬══════════╬════════════════╣
║ 3          ║ Vishwa    ║ Shukla   ║ Admin          ║
╚════════════╩═══════════╩══════════╩════════════════╝

I am not getting any data for Employee 4 & 5 because EmployeeID = 4 & 5 doesn't exists in Employee Table. I want an output for 4 & 5 EmployeeID as below.

╔════════════╦═══════════════╦══════════╦════════════════╗
║ EmployeeID ║ FirstName     ║ LastName ║ DepartmentName ║
╠════════════╬═══════════════╬══════════╬════════════════╣
║ 1          ║ Shahab        ║ Haidar   ║ IT             ║
╠════════════╬═══════════════╬══════════╬════════════════╣
║ 2          ║ Aditya        ║ Asija    ║ HR             ║
╠════════════╬═══════════════╬══════════╬════════════════╣
║ 3          ║ Vishwa        ║ Shukla   ║ Admin          ║
╠════════════╬═══════════════╬══════════╬════════════════╣
║ 4          ║ No Data Found ║          ║                ║
╠════════════╬═══════════════╬══════════╬════════════════╣
║ 5          ║ No Data Found ║          ║                ║
╚════════════╩═══════════════╩══════════╩════════════════╝

Note - EmployeeID would be provide in parameter as '1,2,3,4,5'. we can not change that. Requesting you to kindly help me to resolve this!

Handle these display requirements in your application code (eg: PHP, C++). Simply check if the data is not found for a employee id, display "no data found" — Madhur Bhaiya, Jul 12 '19 at 09:52
'EmployeeID would be provide in parameter' - from where do you only have mysql or is there some other tech in play? — P.Salmon, Jul 12 '19 at 09:54
i've posted a answer, but like @MadhurBhaiya suggested about handling it in the application is much more easy and better.. Topicstarter when you see the needed query iam pretty sure you agree on that also .. — Raymond Nijland, Jul 12 '19 at 10:08

score 1 · Answer 1 · answered Jul 12 '19 at 10:06

The query below does what you require, but like @MadhurBhaiya suggested this is much beter handled in the application as this is a display requirement..

When you see the query you also know why.

Query

SELECT 
    Employee.EmployeeID
  , Employee.FirstName
  , Employee.LastName
  , Employee.DepartmentName
FROM (
  SELECT 
   1 AS EmployeeID
  UNION
  SELECT
    2 AS EmployeeID
  UNION
  SELECT
    3 AS EmployeeID  
  UNION
  SELECT
    4 AS EmployeeID    
  UNION
  SELECT
    5 AS EmployeeID     
) AS search_filter
INNER JOIN
 Employee
ON
 search_filter.EmployeeID = Employee.EmployeeID

UNION ALL 

SELECT 
     search_filter.EmployeeID
  , 'No Data Found' AS FirstName
  , 'No Data Found' AS LastName
  , 'No Data Found' AS DepartmentName
FROM (
  SELECT 
   1 AS EmployeeID
  UNION
  SELECT
    2 AS EmployeeID
  UNION
  SELECT
    3 AS EmployeeID  
  UNION
  SELECT
    4 AS EmployeeID    
  UNION
  SELECT
    5 AS EmployeeID     
) AS search_filter
LEFT JOIN
 Employee
ON
 search_filter.EmployeeID = Employee.EmployeeID
WHERE
 Employee.EmployeeID IS NULL

Result

| EmployeeID | FirstName     | LastName      | DepartmentName |
| ---------- | ------------- | ------------- | -------------- |
| 1          | Shahab        | Haidar        | IT             |
| 2          | Aditya        | Asija         | HR             |
| 3          | Vishwa        | Shukla        | Admin          |
| 4          | No Data Found | No Data Found | No Data Found  |
| 5          | No Data Found | No Data Found | No Data Found  |

see demo

Hi @Raymond Thanks for your reply.. This query will only work when we hard code the values which is in parameter. can we split the EmployeeID which is supplied in paramter and insert it into a temp table and then use join to get this result? — Shahab Haidar, Jul 12 '19 at 10:20
I am not sure how to write inline table valued functions in mysql to split values. — Shahab Haidar, Jul 12 '19 at 10:21
*"This query will only work when we hard code the values which is in parameter"* @ShahabHaidar which you also would do for `IN()` right? .. *"can we split the EmployeeID which is supplied in paramter and insert it into a temp table and then use join to get this result?"* Yes that is possible. — Raymond Nijland, Jul 12 '19 at 10:22

P.Salmon · Answer 2 · 2019-07-12T10:55:30.247

If you have a handy table with lots of incrementing id numbers with no gaps then you could co-opt it as the driving table. For example I have a dates table which has lots of id numbers

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.00 sec)

so with this data

drop table if exists employee,department;
create table employee(employeeid int, firstname varchar(10),departmentid int);

create table department(departmentid int,departmentname varchar(10));

insert into employee values(1,'aaa',1),(2,'bbb',2),(3,'ccc',3);
insert into department values (1,'dep1'),(2,'dep2'),(3,'dep3');

This query drives from dates and since we know that dates contains all the possible values in the in statement then left joins should provide the output we want without hard coding.

Select dt.ID,a.FirstName,b.DepartmentName
from dates dt
left join Employee as a on dt.id = a.employeeid
left Join Department as b On a.DepartmentID=b.DepartmentID
Where dt.ID in (1,2,3,4,5);

+----+-----------+----------------+
| ID | FirstName | DepartmentName |
+----+-----------+----------------+
|  1 | aaa       | dep1           |
|  2 | bbb       | dep2           |
|  3 | ccc       | dep3           |
|  4 | NULL      | NULL           |
|  5 | NULL      | NULL           |
+----+-----------+----------------+
5 rows in set (0.00 sec)

The only complexity is in the creation of the table to co-opt (if needed).

or if you don't want a table you could generate id numbers dynamically (up to 10k) (credit to generate an integer sequence in MySQL @unreason)

select row,a.FirstName,b.DepartmentName
from
(SELECT @row := @row + 1 AS row FROM 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(SELECT @row:=0) numbers
) n
left join Employee as a on n.row = a.employeeid
left Join Department as b On a.DepartmentID=b.DepartmentID
Where n.row in (1,2,3,4,5);

+------+-----------+----------------+
| row  | FirstName | DepartmentName |
+------+-----------+----------------+
|    1 | aaa       | dep1           |
|    2 | bbb       | dep2           |
|    3 | ccc       | dep3           |
|    4 | NULL      | NULL           |
|    5 | NULL      | NULL           |
+------+-----------+----------------+
5 rows in set (0.01 sec)

You might want to make it a temporary table It will prevent other MySQL users possible messing up your results by adding/update/removing, especially when other MySQL users also have acces to the "driving" table... — Raymond Nijland, Jul 12 '19 at 10:31
@Raymond Nijland There are other ways to generate sequence numbers dynamically for example - https://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql which could possibly be used instead of a table — P.Salmon, Jul 12 '19 at 10:49
I know i used those methods in the past.. but who said the topicstarter needs to have a number generator as it is just searching... — Raymond Nijland, Jul 12 '19 at 10:57

How to get non existing value supplied in ` where in statement` as custom provided value in mysql?

2 Answers2