Search code examples
mysqlwith-clause

mySQL version of 'with' clause

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!

Solution

  • You can do it with a sub-select:

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