exists and using x

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

what is meaning of select x means

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

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


i checked below link
https://www.geeksforgeeks.org/sql-exists/

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
Comment
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
Commented:
>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
Commented:
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

Commented:
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.

SELECT
    COUNT( order_nr )
FROM xyz
WHERE ord_cd = '10'
AND EXISTS (
    SELECT  'x'   << this clause is ignored, common options are:   *  1  NULL,
   FROM ABC a
    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