What is Select 'X'?

Question

 sSQL.Append(" SELECT 'X' ");
        sSQL.Append(" FROM ProfileInsurancePlanYear ");
        sSQL.Append(" WHERE ProfileID = " + profileid.ToString() + " AND CropYear = " + cropyear.ToString());

This was a query that was originally hitting an access back end. I have moved it over to SQLCE and am perplexed about what this query is supposed to do.

The table structure it hits is:

ProfileID
InsurancePlanID
CropYear
INsurance_Price
Levels_XML

I am assuming this would select something from the Levels_XML column where the profileid and cropyear match?

Does this even work in sqlCE?

Agc

Answer 1

This type of query is typically used to see if a row exists. If a row is found, the query will return a single character, X. Otherwise, it will be an empty result set... You could also say

 sSQL.Append(" SELECT count(*) ");
 sSQL.Append(" FROM ProfileInsurancePlanYear ");
 sSQL.Append(" WHERE ProfileID = " + profileid.ToString() + 
             " AND CropYear = " + cropyear.ToString());

Which will return a result with either 0 or some positive number. Different approaches both asking the database simply to indicate whether or not any records existing matching the condition.

Marche

COUNT could be expensive and is a poor example. SELECT 1 WHERE EXISTS (..query here..) would be better and checks for 0 or 1 rows – Bloody

To be completely pedantic, gbn's query will return an empty result set if no rows satisfy the "query here" query and will return a result set with one row if there are one or more rows that satisfy the query. SQL server is smart enough to stop once it finds a qualifying row and doesn't need to materialize the entire inner query. – Crossfertilization

I am not recommending COUNT(*), I agree it is not the best, but I think it is a bit clearer to explain what is being achieved by SELECT 'X'. – Marche

Selecting 'x' is selecting a static string of 'x' if the record exists. Selecting COUNT, which uses a function, is more expensive. – Gregarious

Does this bring any optimization? If so, how ? – Amara

It will bring optimization in that SQL does not need to read any rows, so it will hopefully use only the index, and not need to visit the data table at all – Marche

Answer 2

In general, Select 'X' is used with the EXISTS, as the EXISTS predicate does not care about the values in the rows but just if those rows exist.

For example:-

Q.Find employees who have at least one person reporting to them.

SELECT last_name, employee_id FROM employee outer WHERE 
EXISTS (SELECT 'X' FROM employee manager_id=outer.employee_id)

Polydeuces