mySQL version of 'with' clause

Question

The object of my query is to search for a long string in a database. To speed up this process, all records of the longstring table have a hash of that string on the same record. I want to first find all records in the table where my search string's hash is equal to a hash on the longstring table. Then after I have that dataset, I want to compare the actual strings (since hashes aren't always unique).

Now in oracle or mssql I would do this...

with dataset as (
  select long_string
  from longstring
  where hash = 'searchhash'
) select *
from dataset
where long_string = 'searchstring'

... but mysql doesn't support 'with' clauses. So what is my best alternative in mysql?

Thanks in advance!

For Reference: http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — OMG Ponies, Dec 14 '09 at 19:37

score 4 · Accepted Answer · answered Dec 14 '09 at 19:35

4

You can do it with a sub-select:

select *
from (
  select long_string
  from longstring
  where hash = 'searchhash'
) AS dataset
where long_string = 'searchstring'

answered Dec 14 '09 at 19:35

True Soft

8,506
6
51
79

score 2 · Answer 2 · answered Dec 14 '09 at 19:36

2

This is the same as an AND clause.

SELECT  *
FROm longstring
WHERE hash = 'searchhash'
AND long_string = 'searchstring'

answered Dec 14 '09 at 19:36

Adriaan Stander

156,697
29
278
282

mySQL version of 'with' clause

2 Answers2