subquery to simluate table rows? 2

[shadedecho]

So, let's say I had a table (call that "bar") that had one column in it, and like 5 rows, each with a different string value, like 'abc', 'def', ... etc.

I could LEFT JOIN against "bar" from another table (call that "foo"), which only happens to have a subset of those string values as rows in it. What I'd get is a result set that had *all* the string values, one in each row, even if it only appeared in "bar" but not "boo".

Since the values in the theoretical "bar" table are finite (at most 5 or 6), I'd like to avoid needing that actual lookup table, and instead accomplish the above by replacing a reference to "bar" in my LEFT JOIN with a *subquery* that somehow returns a set of rows, one with each of the finite values.

I'm having trouble figuring out how to construct this subquery in such a way that it returns the finite values vertically (that is, as rows) instead of as columns, or inside an IN operator, or something like that.

For instance, you can do a query like this:

SELECT 1;

with no table or where or anything. So, I want a query that can do something like that, but return more than one row, as explicit finite values and not drawing from a table.

Anyone have any thoughts about this?

 

[DonQuichote]

Sorry. After reading your post 3 times, I still cannot grasp what your problem is. Is it premature optimization? In that case: don't. MySQL (as most database servers) can and does optimize the queries itself.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[shadedecho]

Ok, let's say my absolute finite subset of strings is "abc", "def", "ghi", and "jkl".

I have a query which selects from table "foo" (which only has rows with a subset of that possible set of finite string values in its rows). This result set might look like this:

'foo':
| str    |   val
|---------------
| abc    |     3
| def    |     8
| jkl    |     5

Notice that "ghi" is missing from that table result set. But, the ending result set that i want to have needs to have all the possible string values in it.

So I can accomplish that by simply LEFT JOIN'ing with a "bar" table that has one row for each of those finite string values.

That table might look like this:

'bar':
| str
|-----
| abc
| def
| ghi
| jkl

The SQL would essentially be:

SELECT bar.str, foo.val FROM bar LEFT JOIN foo ON foo.str = bar.str

That accomplishes a result set that might look something like this:

| str    |   val
|---------------
| abc    |     3
| def    |     8
| ghi    |  null
| jkl    |     5

On the same page so far?

-----------------------------------------------
OK, so now, since the string values are finite and pre-known, I want to create some query which can dynamically generate "bar" above, instead of actually needing that table to exist only to left-join against.

Such a query could be used in a sub-query inside of my LEFT JOIN above, in place of a reference to the actual 'bar' table.

Does this make more sense now?

 

[DonQuichote]

No. You show the values from bar with a related field from table foo. That is what the left join is. Those strings are "pre-known" in the table bar and looked up very fast with an index on table foo. No temporary result can do this faster, especially if it has to be parsed first.

Please learn to trust SQL engines. They do a great job. One of the reasons that it is great that SQL is a functional language is that you only have to specify what you want and leave the how to the SQL parser/optimizer. If you have your keys well defined and your database scheme well defined your queries will run like a hurricane. The more complicated you make your queries, the harder you make it for the optimizer to optimize it.

If you want to speed things up, you could always use integer keys instead of string keys.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[DonQuichote]

Well, if you have any other reasons to want an "on the fly" resultset, you can do something really ugly like:

SELECT COUNT(0) FROM (SELECT 'abc' AS dummy UNION SELECT 'def' UNION SELECT 'ghi') AS inline

You must give the on-the-fly table a name, like "inline" above. But do not use it on your example. It is illegible, therefore unmaintainable and slow. No sane programmer or even optimizer can or will help you if you do such things.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[shadedecho]

@DonQuichote - Thank you, I think that UNION concept will accomplish what I was looking for. I'll test it out.

I never said I was trying to optimize a query or second-guess the sql engine. In fact, this question is part of a much more complex set of query calculations I'm doing, and I *know* it's not particularly optimal. It is precisely *because* I know that SQL can do things really fast that I'm endeavoring to do all of my task completely with one (complex) SQL query (with subqueries and JOINs).

The "source" of those strings is unfortunately external to my system (and database) in question, and therefore it's awkward (and inefficient) to first just throw them temporarily into a table just to JOIN against the list, when instead I can use the external scripting control to dynamically construct the single query string that I can hand off to mysql and let it run.

I did think about creating a temp table for this purpose, since it'll automatically get thrown away at the end of that execution thread, but my guess was a sub-query (even one with a few simple UNIONs) would execute as quick or quicker than a separate initial request just to create a temp table.

Even unoptimized as it will be, the single chunk of SQL I'm constructing will surely still operate faster than if I needed to pull all the values from the various temporary steps out into an external scripting language one at a time, for processing and then pushing the values back into the database in subsequent queries. So, I want a single query to do it all, to keep it all inside mysql, ironically *for* efficiency sake.

The plus side is that this query will run infrequently, so it's internal mysql performance/optimization is not as big of a deal, as long as it doesn't drag down the external scripting thread which fires it off.

 

[r937]

what's wrong with this --

SELECT bar.str
     , foo.val 
  FROM ( SELECT 'abc' AS str
         UNION ALL SELECT 'def'
         UNION ALL SELECT 'ghi'
         UNION ALL SELECT 'jkl'
       ) AS bar 
LEFT OUTER
  JOIN foo 
    ON foo.str = bar.str

i disagree that this is "illegible, therefore unmaintainable and slow"

i also disagree that "No sane programmer or even optimizer can or will help you"

this is fairly routine stuff for a small set of constant values



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[shadedecho]

thank you r937. that's what I'm going to do, thanks to you and Don.