How to Select Exists Directly as a Bit

is it possible to select EXISTS directly as a bit?

No, you'll have to use a workaround.

If you must return a conditional bit 0/1 another way is to:

SELECT CAST(
   CASE WHEN EXISTS(SELECT * FROM theTable where theColumn like 'theValue%') THEN 1 
   ELSE 0 
   END 
AS BIT)

Or without the cast:

SELECT
   CASE
       WHEN EXISTS( SELECT 1 FROM theTable WHERE theColumn LIKE 'theValue%' )
            THEN 1 
       ELSE 0 
   END

Select IF EXISTS as a BIT column

You were almost there. Try this.

SELECT
     d.Region_Name
    , d.Dealer_Name
    , RIGHT(r.Vin,8) AS VIN
    , CASE WHEN EXISTS (SELECT 1 FROM tblVinDisabledDate WHERE Vin = r.Vin)
      THEN CAST(1 AS BIT)
      ELSE CAST(0 AS BIT) END
FROM tblCCDealerCall_Customer c
JOIN tblCCDealerCall d ON c.DealerID = d.DealerID
JOIN tblReservation_TestDrive r ON r.CustomerID = c.CustomerID

If you want to avoid a correlated query, join the other table as well.

SELECT
      d.Region_Name
    , d.Dealer_Name
    , RIGHT(r.Vin,8) AS VIN
    , CASE WHEN vd.vin is not null then CAST(1 AS BIT) ELSE CAST(0 AS BIT) END 
FROM tblCCDealerCall_Customer c
JOIN tblCCDealerCall d ON c.DealerID = d.DealerID
JOIN tblReservation_TestDrive r ON r.CustomerID = c.CustomerID
LEFT JOIN tblVinDisabledDate vd ON vd.Vin = r.Vin

SQL select EXIST over multiple tables as Boolean (Bit)

I am guessing that your code works. You can do a couple things to improve it. First, the cast() isn't necessary because you can put in bit literals directly (see here). The following code uses both methods for expressing a bit literal.

Second, don't use single quotes for column names (and, of course, mysql_ is deprecated, but that is another matter because this just focuses on the query). In fact, you should avoid using reserved words as column names. So:

SELECT (CASE WHEN 
         EXISTS(select 1 from codes where code = '".$generatedCode."') OR 
         EXISTS(select 1 from pakete where code = '".$generatedCode."') OR
         EXISTS(select 1 from kunden where code = '".$generatedCode."') OR
         EXISTS(select 1 from formulare where code = '".$generatedCode."') OR  
         EXISTS(select 1 from berater where code = '".$generatedCode."') 
         THEN 0b1 
         ELSE  b'0' 
       END) as CodeExists

By the way, I would discourage you from using bits, unless you really know what you are doing in terms of optimization. They may not save space and they may not run faster. The following seems totally reasonable to me:

SELECT (EXISTS(select 1 from codes where code = '".$generatedCode."') OR 
        EXISTS(select 1 from pakete where code = '".$generatedCode."') OR
        EXISTS(select 1 from kunden where code = '".$generatedCode."') OR
        EXISTS(select 1 from formulare where code = '".$generatedCode."') OR  
        EXISTS(select 1 from berater where code = '".$generatedCode."') 
       ) as CodeExists

Where with exists how it works in subselect with variable without link

where @var is null is a boolean condition

if @var is null, then it's true - else false.

or exists (select '' from table t 
           where t.id = s.id and date > '2012-12-1')

returns true, if at least one row exists for the subquery.

If @var is not null, then exists is evaluated for a true condition

Optimizing SELECT COUNT to EXISTS

select case
         when exists (select *
                      from   customer
                      where  amount <> 0
                             and customerid = 22) then 1
         else 0
       end  as non_zero_exists

What's the best to check if item exist or not: Select Count(ID)OR Exist(...)?

EXISTS, always

• COUNT will traverse the table or an index: you asked for a COUNT
• EXISTS will stop as soon as it finds a row

Edit, to be clear

Of course, in this case if the email column is unique and indexed it will be close.

Generally, EXISTS will use less resources and is more correct too. You are looking for existence of a row, not "more than zero" even if they are the same

Edit2: In the EXISTS, you can use NULL, 1, ID, or even 1/0: it isn't checked...

21 May 2011 edit:

It looks like this was optimised in SQL Server 2005+ so COUNT is now the same as EXISTS in this case

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.

---

Related Topics