Jump to content

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


ChenXiu

Recommended Posts

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 :-)

Link to comment
Share on other sites

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

???

Link to comment
Share on other sites

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.


 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.