Apr 1, 2013

IF statement or condition in WHERE clause of SELECT statement in sql server

How to use or ass IF statement or condition in WHERE clause of SELECT statement in SQL server

We cannot use IF statement or condition in WHERE clause of the SELECT statement. It is syntactically incorrect.  We can achieve our goal without using if condition in WHERE clause. For example:

(1) Suppose we want to execute the following query in SQL server:

SELECT *
FROM Student
WHERE
    IF Age < 20 BEGIN
         Country = 'USA'
    END ELSE IF Age = 20 BEGIN
         Counry = 'UK'
    END ELSE BEGIN
         Country = 'India'
    END

Its equivalent query in SQL server
   
SELECT *
FROM Student
WHERE
(Age < 20 AND Country = 'USA') OR
(Age = 20 AND Country = 'UK') OR
(Age > 20 AND Country = 'India')

(2) Suppose we want to execute the following query in SQL server:

SELECT *
FROM Student
WHERE RollNo > 5
    AND RollNo < 50
    AND IF Age < 20 BEGIN
         Country = 'USA'
    END ELSE IF Age = 20 BEGIN
         Counry = 'UK'
    END ELSE BEGIN
         Country = 'India'
    END

Its equivalent query in SQL server
   
SELECT *
FROM Student
WHERE RollNo >
    AND RollNo < 150
    AND(
    (Age < 20 AND Country = 'USA') OR
    (Age = 20 AND Country = 'UK') OR
    (Age > 20 AND Country = 'India'))

(3) Suppose we want to execute the following query in SQL server:

SELECT *
FROM Student
WHERE IF Age > 10  AND AGE < 20 BEGIN
         Country = 'USA'
    END

Its equivalent query in SQL server
   
SELECT *
FROM Student
WHERE
    ((Age > 10 AND AGE < 20 ) AND Country = 'USA') OR
    (Age <= 10 OR Age >= 20)

If you still think that any condition which is possible using IF statement in WHERE clause of a SELECT statement but not possible by using AND or OR operator you can ask ii in the comment section I will write that.

11 comments:

  1. Anonymous4/06/2013

    Thanks...Finally got the hang of conditional statements in WHERE clauses for SQL.

    ReplyDelete
  2. Anonymous7/29/2013

    Thank yoy so so Much. I had been finding for a solution for this from a long time.Thanks a million

    ReplyDelete
  3. Anonymous8/16/2013

    select d.Document_Name, d.Entry_Date, d.Person_ID, cv.Value, dc.Cust_String_Val
    from Document as d, Document_Custom as dc, Custom_FixedValues as cv
    where cv.Custom_ID = dc.Custom_ID
    and dc.Document_ID = d.Document_ID
    and d.Document_Name = '(HR3) Education- ABEL, LAURIE'
    if cv.Value = 'A' begin cv.Value = 'null' end
    else if cv.Value = 'I' begin cv.Value = 'null' end
    else begin cv.Value = cv.Value end

    ReplyDelete
    Replies
    1. select d.Document_Name, d.Entry_Date, d.Person_ID, cv.Value, dc.Cust_String_Val
      from Document as d, Document_Custom as dc, Custom_FixedValues as cv
      where cv.Custom_ID = dc.Custom_ID
      and dc.Document_ID = d.Document_ID
      and d.Document_Name = '(HR3) Education- ABEL, LAURIE'
      ((cv.Value IN('I', 'A') AND cv.Value = 'null') OR
      (cv.Value NOT IN('I', 'A'))
      )

      Delete
  4. Anonymous4/17/2014

    SELECT a,b,c
    FROM Documents as d
    WHERE
    IF (@input !='') CONTAINS(a, @input)

    ReplyDelete
    Replies
    1. SELECT a,b,c
      FROM Documents as d
      WHERE ((@input !='' AND a LIKE '%' + @input + '%' ) OR @input = '')

      Delete
    2. Anonymous5/05/2014

      But that's not using FullText search, so you will get different results.

      Delete
  5. Declare @paramFromDestination nvarchar(10)= 'LHR'
    ,@paramToDestination nvarchar(10)= 'DPS'
    ,@paramAirline nvarchar(10)= null
    ,@paramFromDate datetime= '2014-07-25'
    ,@paramToDate datetime= '2014-08-28'
    ,@paramExclude bit= 0

    print(@paramExclude)
    SELECT
    [AirOfferBlackout_FromDestination]AS FromDestination
    ,[AirOfferBlackout_ToDestination]AS ToDestination
    ,[AirOfferBlackout_Airline]AS Airline
    ,[AirOfferBlackout_OutBoundFromDate]AS OutBoundFromDate
    ,[AirOfferBlackout_OutBoundToDate]AS OutBoundToDate
    ,[AirOfferBlackout_InBoundFromDate]AS InBoundFromDate
    ,[AirOfferBlackout_InBoundToDate]AS InBoundToDate

    FROM [AirOfferBlackout]
    WHERE ([AirOfferBlackout_FromDestination] = COALESCE(@paramFromDestination,[AirOfferBlackout_FromDestination]) OR [AirOfferBlackout_FromDestination] = 'ANY')
    AND ([AirOfferBlackout_ToDestination] = COALESCE(@paramToDestination,[AirOfferBlackout_ToDestination]) OR [AirOfferBlackout_ToDestination] = 'ANY')

    AND ([AirOfferBlackout_Type] = COALESCE(@paramExclude,1))
    AND ([AirOfferBlackout_Airline] = COALESCE(@paramAirline,[AirOfferBlackout_Airline]) OR [AirOfferBlackout_Airline] = 'ANY')
    AND
    (
    ((@paramFromDate > (CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106)) AND @paramFromDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106))) AND ((@paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106))) AND @paramToDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106))))

    OR
    (
    if(((CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106)) = '01-01-1900') AND ((@paramToDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundFromDate],106))))
    if ((@paramToDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundFromDate],106))) OR (@paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106)))))

    )
    --OR
    --if((CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106) = '01-01-1900') AND ((@paramFromDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundFromDate],106))) AND (@paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106)))))
    --if (@paramFromDate > (CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106)) AND (@paramFromDate < (CONVERT(varchar(20),[AirOfferBlackout_InBoundToDate],106))))
    --OR

    -- if((CONVERT(varchar(20), [AirOfferBlackout_InBoundFromDate],106)) = '01-01-1900')
    -- if ((@paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_InBoundFromDate],106))) AND @paramToDate > (CONVERT(varchar(20),[AirOfferBlackout_OutBoundFromDate],106)))
    )

    ReplyDelete
  6. Sir , i have try to use case instead of if but i have nested if statements so please explore it

    ReplyDelete
  7. Thank you very much. Seeing these gave me ideas for what I was trying to do!

    ReplyDelete