How to make an empty result in a union query NULL

[idkwhy]

I have been asking around but nobody seems to know the answer. I'm wondering if anyone here may know.

In a query with multiple unions, is there a way to manually set their result set to null if no results are found for it?

 

To elaborate..In a typical union query you may have several queries, like

 

SELECT * FROM `table1` WHERE `id` = '1'
UNION
SELECT * FROM `table2` WHERE `id` = 1
UNION
SELECT * FROM `table3` WHERE `id` = 2
UNION
SELECT * FROM `table4` WHERE `id` = 3

Really basic example, but you get the point

 

Assuming each table has 3 columns called col1, col2, col3, the result set may look like...

----------------------------
|  col1  |  col2  |  col3  |
----------------------------
|  value |  value |  value |
----------------------------
|  value |  value |  value |
----------------------------
|  value |  value |  value |
----------------------------

But what if I wanted the second (empty) query to still be in the result set? Instead of it being dropped, can it appear with null? Like..

----------------------------
|  col1  |  col2  |  col3  |
----------------------------
|  value |  value |  value |
----------------------------
|  NULL  |  NULL  |  NULL  |
----------------------------
|  value |  value |  value |
----------------------------
|  value |  value |  value |
----------------------------

Is this possible? Edited January 19, 2014 by idkwhy

[idkwhy]

I accidentally edited out that the second query
 

SELECT * FROM `table2` WHERE `id` = 1

should return an empty set

 

It isn't letting me edit it back in, so I wanted to clarify that

[Psycho]

Yes but, really, I can think of no good reason why you would need that. I have to assume you are building the code that processes that data to 'assume' that there will be a record for each table and are also 'assuming' that the first record is from table one, the second from table two, etc.

 

Never build code that assumes data to exist as certain positions like that. Better to explicitly state where the data is coming from. using your example, I would include another parameter in the SELECT statements to explicitly determine where the data is coming from:

SELECT *, 'table1' as tableName FROM `table1` WHERE `id` = '1'
UNION
SELECT *, 'table2' as tableName FROM `table2` WHERE `id` = 1
UNION
SELECT *, 'table3' as tableName FROM `table3` WHERE `id` = 2
UNION
SELECT *, 'table4' as tableName FROM `table4` WHERE `id` = 3

Now you can process the results and easily determine which tables had records and which ones did not.

 

 

But, to answer your question directly, you could do it, but the queries would be more complicated and not efficient

SELECT * FROM (SELECT 'table1' AS tableName) AS t1
LEFT JOIN `table1` WHERE `id` = '1'
UNION
SELECT * FROM (SELECT 'table2' AS tableName) AS t2
LEFT JOIN `table1` WHERE `id` = '1'
UNION
SELECT * FRM (SELECT 'table3' AS tableName) AS t3
LEFT JOIN `table1` WHERE `id` = '2'
UNION
SELECT * FROM (SELECT 'table4' AS tableName) AS t4
LEFT JOIN `table1` WHERE `id` = '3'

Edited January 19, 2014 by Psycho

[idkwhy]

Thank you so much @Psycho

 

I didn't plan to write the final, working query selecting all columns mindlessly without declaring which belongs to what. I'm strict about avoiding the * to begin with, this was purely for example, to try and explain what I'm trying to do. I really appreciate that you mentioned that though, it's very good advice

 

LEFT JOINs are a really good idea, but so inefficient, like you said, that I didn't consider it.

 

Rarely in my application do I ever search for retrieving null if the query fails, but in this case, I'm hardpressed. Perhaps if I explain the reason why, you can offer further advice.

 

In this specific area of my application, I'm checking to determine if the user has things associated with their account, in a specific order. If the order was thrown off because the person didn't have one thing associated with their account, but had the next thing, the things they have would take the place of the things they don't have.

 

The alternative I realized could be to run the queries separately without the UNION, but I really don't want to make multiple small queries. For efficiency purposes, I'd really rather it all be combined to one query. But.. if that is my only alternative.. I wonder if it is actually more efficient to make multiple small queries instead

 

I can explain more if I've confused you

Edited January 19, 2014 by idkwhy

[kicken]

You can use your union query, and you don't need the left joins to make a null result set. As mentioned, add another column to identify the queries in the results and then modify your code to check for the specific results.

 

For example, since you mention needing them in a particular order and check for anything missing, you can just number the queries and as you read the results make sure the numbers are contiguous.

SELECT 1 as resultNumber FROM `table1` WHERE `id` = '1'
UNION
SELECT 2 as resultNumber FROM `table2` WHERE `id` = 1
UNION
SELECT 3 as resultNumber FROM `table3` WHERE `id` = 2
UNION
SELECT 4 as resultNumber FROM `table4` WHERE `id` = 3
ORDER BY resultNumber

$count=0;
while ($row=$query->fetch()){
   $count++;
   if ($count != $row['resultNumber']){
      //Gap found, something is missing
   }
}

[idkwhy]

This is also a really good idea, thank you @kicken
 
Just in case someone else in the future needs this help too, I'm going to leave what else I was told, that may help..
 

SELECT count(id), id, col1, col2, col3 FROM table1 WHERE id=1
UNION
SELECT count(id), id, col1, col2, col3 FROM table2 WHERE id=1

http://sqlfiddle.com/#!2/259d6/9/0