Using boolean expression outside of a where/having clause

I have the following data:

store_id    store_name  phone   email   street  city    state   zip_code
1   Santa Cruz Bikes    (831) 476-4321  santacruz@bikes.shop    3700 Portola Drive  Santa Cruz  CA  95060
2   Baldwin Bikes   (516) 379-8888  baldwin@bikes.shop  4200 Chestnut Lane  Baldwin NY  11432
3   Rowlett Bikes   (972) 530-5555  rowlett@bikes.shop  8000 Fairway Avenue Rowlett TX  75088

If I write a query such as:

select * from sales.stores where email='rowlett@bikes.shop'
-- store_id store_name  phone   email   street  city    state   zip_code
-- 3    Rowlett Bikes   (972) 530-5555  rowlett@bikes.shop  8000 Fairway Avenue Rowlett TX  75088

I get the expected result with one row. However, if I use the exact same expression in the select list, I get what seems like gibberish:

SELECT email='rowlett@bikes.shop' from sales.stores where email='rowlett@bikes.shop'
-- email
-- rowlett@bikes.shop

Why does SQL Server return the result rowlett@bikes.shop for the expression email=rowlett@bikes.shop ? I understand it doesn’t have the boolean type, but I’d think it would be converted to something like 1. Why does that occur?

>Solution :

As @Martin Smith pointed out in the comments, whether you use:

SELECT email = 'rowlett@bikes.shop' ...

or

SELECT 'rowlett@bikes.shop' AS email ...

you’ll get the same query result – that is, you’ll get a column named ’email’ in your query resultset, with ‘rowlett@bikes.shop’ as the value for each row.

|email               |column2|column3|
 -------------------- ------- -------
|'rowlett@bikes.shop'|value1 |another value1|
|'rowlett@bikes.shop'|value2 |another value2|
etc.

To go one step further beyond your question (making the assumption that you actually do want to have a true/false value returned in your query results), then you can use a CASE expression to achieve that – eg.

SELECT
CONVERT(bit,
CASE 
  WHEN email = 'rowlett@bikes.shop' 
  THEN 1 
  ELSE 0 
END
)
FROM sales.stores;

And expanding that to see some potential results:

SELECT
email,
CONVERT(bit,
CASE 
  WHEN email = 'rowlett@bikes.shop' 
  THEN 1 
  ELSE 0 
END
) AS EmailCheck
FROM sales.stores;

you’d see something like:

|email                |EmailCheck |
|'rowlett@bikes.shop' |1 |
|'someone@xyz.com'    |0 |
|'someoneelse@xyz.com'|0 |