Best gaming deals on Amazon | This weeks Xbox deals with gold

case when vs exists in sql

Category: sql server getstarted

Question

jaweher89 on Fri, 18 Mar 2016 09:41:36




I have the following query

  SELECT top 10 case
         when exists (SELECT  t1.[Code Article]
                                                      FROM     [Promotion]  

                                                      where t1.datecol between 
                                                             [Date Debut Promo] and  [Date Fin Promo])
         then 1
         else 0
       end as test1
       , Case when  [Code Article] in   (SELECT   [Code Article]
                                                      FROM     [Promotion]  

                                                      where  datecol between 
                                                             [Date Debut Promo] and  [Date Fin Promo])then 1 else 0 end as test2

  FROM [QlikDataWarehouse].[dbo].[Vente]t1

Results

enter image description here

Why I get test1<>test2 ? what is the diffrence between test1 and test2 ? It is not the same ?

Replies

Olaf Helper on Fri, 18 Mar 2016 09:46:41


The sub-query of the second case, does it return a NULL value? Then the IN Operation will always result in "undefined" and your query returns 0.

Dan Guzman on Fri, 18 Mar 2016 10:29:44


The sub-query of the second case, does it return a NULL value? Then the IN Operation will always result in "undefined" and your query returns 0.

Olaf, perhaps you thinking of NOT IN.  NOT IN will return unknown rather than true or false when the subquery returns NULL but IN will return only true or false.

Eric__Zhang on Tue, 22 Mar 2016 06:16:07


Hi jaweher89,

Let's put the NOT IN or EXISTS aside, I think your problem is a typo in your query, in the first CASE expression the where condition is "t1.datecol between" and in the second CASE  it is "datecol between", they refer to different columns from two tables.

Check a simple demo below.

DECLARE @Vente TABLE([Code Article] varchar(20),datecol date)
DECLARE @Promotion TABLE([Code Article] varchar(20),datecol date,[Date Debut Promo] DATE,[Date Fin Promo] DATE)

INSERT INTO @Vente VALUES('Code1','20150101')
INSERT INTO @Promotion VALUES('Code1','20150601','20150101','20150102')

SELECT TOP 10 CASE 
		WHEN EXISTS (
				SELECT t1.[Code Article]
				FROM @Promotion
				WHERE t1.datecol BETWEEN [Date Debut Promo]
						AND [Date Fin Promo]
				)
			THEN 1
		ELSE 0
		END AS test1
	,CASE 
		WHEN [Code Article] IN (
				SELECT [Code Article]
				FROM @Promotion
				WHERE t1.datecol BETWEEN [Date Debut Promo]
						AND [Date Fin Promo]
				)
			THEN 1
		ELSE 0
		END AS test2
FROM @Vente t1

If you have any feedback on our support, you can click here.

Eric Zhang
TechNet Community Support

Russ Loski on Tue, 22 Mar 2016 08:30:17


In order to confirm or remove Eric's answer from consideration we need the DDL for the tables. Otherwise we have to guess at the source issue.

Latest posts in the category


sharepoint setup_ja

windows phone devtool_ja