exists and using x

gudii9 used Ask the Experts™
when we use exists in a query

what is meaning of select x means

select count(order_nr) from xyz where ord_cd="10"

select 'x' from ABC a where ef=ge
((datediff(...)or (datediff.....))

i checked below link

how to validate my results are correct or not

how to break above query to see if difference of time is actually correct?
please advise
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
>what is meaning of select x means
<Wild guess>  The 'x' doesn't really matter, as the subquery will either return rows or not, and the main query will return rows or not based on whether the subquery returns rows or not.  So it could really read SELECT 1, SELECT SomeColumnName ... doesn't matter.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
With some RDBMS, in the early days 10 years ago or so, it has been important to properly choose what has been selected in an "exists" clause - because it had performance penalities. So folks got into the habit to use constants like 1 or 'X'. Some RDBMS required to use "*" to select all columns for good performance.
Nowadays, it doesn't matter, and many users consider it confusing to put in something different than a simple * or 1 here.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

When using EXISTS the correlated subquery does not actually need to return data, in effect it is only the from & where clauses that do the work. The select clause is there because syntax rules dictate it must be used. Often "select *" or "select 1" or "select NULL" is used but 'x'  is also valid because  it really doesn't matter what this clause contains.

    COUNT( order_nr )
FROM xyz
WHERE ord_cd = '10'
    SELECT  'x'   << this clause is ignored, common options are:   *  1  NULL,
    WHERE XYZ.colx = a.coly  -- all the needed work done here


Using EXISTS forms a "left semi-join"  and has been part of SQL since its inception by  Chamberlin & Boyce in the early 1970's.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial