SQL Confusion

[shae]

Build a table by hand:

SELECT *
FROM (SELECT 'test' AS colname
UNION
SELECT 'blah' AS colname
UNION
SELECT 'root\\branch\\name\\[PMB]' AS colname) AS derivedtable

The result is:

-colname
1blah
2root\\branch\\name\\[PMB]
3test

Now I add a where clause:

SELECT *
FROM (SELECT 'test' AS colname
UNION
SELECT 'blah' AS colname
UNION
SELECT 'root\\branch\\name\\[PMB]' AS colname) AS derivedtable
WHERE (colname LIKE '[PMB]%')

What result do you expect?
I was surprised when I got back only 'blah'.
It took me a few minutes to realize why... do you know the answer?