SELECT EXISTS in Sybase

Question

I have the following query in PostgreSQL (1=1 is a placeholder for some arbitrary condition as apparently I can't write WHERE TRUE in Sybase)

SELECT EXISTS FROM (
    SELECT 1 FROM someTable WHERE 1=1  
)

How do I translate them for SQL Server / Sybase syntax ? A roundabout way is to do:

SELECT COUNT(*) FROM (
    SELECT 1 FROM someTable WHERE 1=1  
) a

… which can further be simplified to:

SELECT COUNT(*) FROM someTable WHERE 1=1

… but EXISTS is cleaner and I believe it's in the ANSI standard as well.

In TSQL, that `where 1=1` is doing absolutely nothing. What are you including it for? — iamdave, Jan 27 '17 at 16:19
@TimSchmelter return TRUE or FALSE depending on whether a row exists in table `someTable` that satisfies some arbitrary condition (`1=1` is a placeholder). — Marcus Junius Brutus, Jan 27 '17 at 16:20

SqlZim · Accepted Answer · 2017-01-27T16:32:45.310

exists() doesn't return a value that you can select (I don't know why). You can check if exists(), but not select exists(). You can also check where exists() or even case when exists().

select 
    E = case 
        when exists(
          select 1 from master..spt_values
        ) 
          then 1
        else 0 
        end

If you are trying to get counts for multiple different criteria, a common pattern for sql server would be something like:

select 
    ACount     = sum(case when x='A' then 1 else 0 end)
  , ABCount    = sum(case when x in ('A','B') then 1 else 0 end)
  , TotalCount = count(*) /* or sum(1) */ 
from someTable

score 1 · Answer 2 · answered Jan 27 '17 at 16:15

1

Not sure what you expect for 'EXISTS' but this might do the trick

SELECT 1 
WHERE EXISTS (SELECT 1 FROM dbo.Table WHERE 1 = 1)

answered Jan 27 '17 at 16:15

Adam Jacobson

564
4
9

This returns an empty result set if the condition does not hold. It is not guaranteed to return TRUE/FALSE. – Marcus Junius Brutus Jan 27 '17 at 16:23
That's why I asked in my comment that I wasn't sure what exists was supposed to do. I could edit my anser but the answer below solves the problem. – Adam Jacobson Jan 27 '17 at 16:31

score 0 · Answer 3 · answered Jan 27 '17 at 16:40

0

Try this:

SELECT IIF(EXISTS (SELECT 1 FROM mytable WHERE 1=1), 1, 0)

answered Jan 27 '17 at 16:40

Giorgos Betsos

71,379
9
63
98

[Code: 156, SQL State: S1000] Incorrect syntax near the keyword 'EXISTS'. – Marcus Junius Brutus Jan 27 '17 at 17:38
@MarcusJuniusBrutus This query runs from SQL Server 2012 onwards. Which version are you using? – Giorgos Betsos Jan 27 '17 at 18:22
Yes, I should have mentioned that in the comment. I ran it on Sybase ASE 15.7 – Marcus Junius Brutus Jan 27 '17 at 18:42

SELECT EXISTS in Sybase

3 Answers3