mySQL "union all" queries on tables with varying number of columns

[ChenXiu]

I have tried several "UNION ALL" queries, and I either get:
• "undefined index" errors or
• when selecting from tables with same column names, I can't differentiate which table the column belongs to
• I get alot of empty results

Here are some examples I have tried:

$query = "SELECT description as ffff, NULL as gggg FROM myTable WHERE sku = '1234'
UNION ALL
SELECT description as ffff, weight as gggg FROM my_other_Table WHERE sku = '1234'"
$result = $db->query($query);
while ($row = $result->fetch_assoc()) {
echo $row['fff'];
echo $ow['gggg'];
}

 

$query = "SELECT description as ffff, NULL as gggg FROM myTable WHERE sku = '1234'
UNION ALL
SELECT color as uuuuuu, weight as pppppppppp FROM my_2nd_Table WHERE sku = '1234'"
UNION ALL
SELECT color as wwxxyyzz, weight as mmmnnnoooppp FROM my_3rd_Table WHERE sku = '1234'"

$result = $db->query($Query);
while ($row = $result->fetch_assoc()) {
echo $row["ffff"];
echo $row["uuuuuu"];
echo $row["pppppppppp"]
echo $row["wwxxyyzz"];
echo $row["mmmnnnoooppp"];
}

I think I am almost on the right 'track' but the train conductor wants his ticket :-)

[Barand]

24 minutes ago, ChenXiu said:

"undefined index" errors

The first query defines the column names for all results in a union, so in your second example you will have columns ffff, gggg only.

 

24 minutes ago, ChenXiu said:

when selecting from tables with same column names, I can't differentiate which table the column belongs to

Use a dummy identifying column

SELECT description as ffff
     , NULL as gggg
     , 'A' as source 
  FROM myTable 
  WHERE sku = '1234'
  
UNION ALL 
SELECT description
     , weight 
     , 'B' 
  FROM my_other_Table 
  WHERE sku = '1234'

[edit]  Why have you got multiple tables with the same columns in the first place?

 

24 minutes ago, ChenXiu said:

I get alot of empty results

???

[ChenXiu]

Barand, thank you.
Okay, I think I understand.
It sounds like if the first query defines the column names, then maybe what I should do is make my first query the longest query with the most actual columns, and set the alias names there to be used for the remaining "union select" subqueries.
Regarding using a "dummy column" to differentiate, I am not sure how PHP echoes a dummy column, would it be something like this:
$result = $db->query($query);
while($row=$result1->fetch_assoc()) {
echo $row["A"."description"];
}
( ...probably not, but I want to demonstrate that I am trying 😀 )

The reason some of the columns have the same names is because I have never used "union select" before, so never needed to differentiate, and there is often lots of duplication amongst the data I receive from various sources (e.g. all the sources seem to provide "color" and "description," of which only one of them may provide "weight" or "dimensions," etc.
So when I built the mySQL tables, I didn't know in advance I would ever need to differentiate between which table I needed, for example, the color from.

I suppose the easiest thing would be to simply rename the table columns so they are all different (takes 5 minutes max to do this), but then I won't learn the new stuff :-)

Regarding empty results -- Yes! For whatever reason, out of the 100's of experiments I did earlier today, while looping through the "while ($row = $result->fetch_assoc())" loops, PHP would always echo empty results in seemingly random spots.
My "fix" on this was this:
$dog = !empty($row["columnName"]) ? $row["columnName"] : "";
$horse = !empty($row["other_columnName"]) ? $row["other_columnName"] : "";
$bird = !empty($row["columnSomething"]) ? $row["columnSomething"] : "";
echo $dog . $bird . $horse, etc., etc.

I really want to learn how to make the, "Union Select" technique work!
I did some benchmark loops of 1000 queries (union 3 queries, versus 3 queries in sequence, ran 1000 times each) and found that "Union Select" is twice as fast!
Instead of taking 30 nanoseconds for a 1000-query loop, it only takes 15 nanoseconds.
That being said, just running a "union query" once will only save me 15 picoseconds.
However, when you get to be my age, every picosecond counts.

 

[kicken]

39 minutes ago, ChenXiu said:

I am not sure how PHP echoes a dummy column, would it be something like this:

From the PHP perspective your dummy column is the same as any other column.  If you want to echo it, you just echo $row['dummyColumnName'].  You probably don't want to echo it though, you want to use it to determine which table the row came from using a simple if.

if ($row['dummyColumnName'] === 'A'){
  //$row is from the first table
} else {
  //$row is from the second table.
}

 

43 minutes ago, ChenXiu said:

I really want to learn how to make the, "Union Select" technique work!

I suspect you might not understand what a union is for.  Generally speaking, you shouldn't need to use a union.  If you do, it might be a sign that you're database design needs to be re-worked.  There are some reasons for it's use, but they are not all that common.  I don't know your data or what you're trying to do but I'd guess that a union select probably isn't the right solution.

 

[Barand]

43 minutes ago, ChenXiu said:

I am not sure how PHP echoes a dummy column

Same as any other column.

echo $row['columnName');

In my example above, you would

echo $row('ffff');
echo $row('gggg'];
echo $row['source'];

 

46 minutes ago, ChenXiu said:

then maybe what I should do is make my first query the longest query with the most actual columns

All the queries in a union must have the same number of columns, and respective columns in each must be of the same type. So you can't select an integer as the first column in one and the select a varchar as the first column in the next query.

[ChenXiu]

Thank you both. My goal was actually to speed up 4 mysql queries to 4 different tables, some sharing the same data, some not. I was looking for a huge speed improvement, but with the complexity of the "union all" style, it is a nightmare for me, especially for keeping track of what data comes from which table.

But I'm glad I learned, and actually executed a real "union" style query!

However, I just now experimented with using "join" instead of "union" and was surprised that using "join" benchmarked in at 3 (THREE) times faster (unlike the meager 2X faster of "union") than doing normal mysql queries of 4 tables in sequence (thus extending the vacation/relaxation portion of my life by an extra 20 picoseconds, instead of 15 picoseconds (golf course, here I come!)).

I'm finding mysql to be more of a religion than a science. Like Excel.
A few have absolutely mastered it, and for them, mysql can do just about everything on the planet. Same with Excel.
The rest of us will never truly get it.
But that's what makes it fun to try and learn.