mySQL version of 'with' clause

Question

Hello!

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!

Answer 1

You can do it with a sub-select:

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

Answer 2

This is the same as an AND clause.

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