Question 20976 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL AND as optional

AND t1.Team IN (SELECT Team FROM t2 WHERE t3.ID = t2.ID)

How can I make above AND as optional, if the sub-query does not have any results, do not even consider AND t1.Team IN ("").

Something like this does not work:

AND IF((SELECT Team FROM t2 WHERE t3.ID = t2.ID), (t1.Team IN (SELECT Team FROM t2 WHERE t3.ID = t2.ID)), 1)
Tags:

4

Answers


  1. 0

    You can use Case Statement for check if else condition inside the SQL query syntax.

    For that I think your query looks like below:

    AND 
  CASE 
    WHEN (SELECT COUNT(*) FROM t2 WHERE t3.ID = t2.ID) > 0 THEN t1.Team IN (SELECT Team FROM t2 WHERE t3.ID = t2.ID)
    ELSE 1=1
  END
    Login or Signup to reply.
  2. 0

    Formally you need in

    AND (    t1.Team IN (SELECT Team FROM t2 WHERE t3.ID = t2.ID)
      OR NOT EXISTS (SELECT Team FROM t2 WHERE t3.ID = t2.ID)
      )

    But moving this subquery to FROM clause with proper left-joining seems to be more reasonable in your case.

    Login or Signup to reply.
  3. 0

    Might be bit faster to do (if t1.Team is not NULL):

    AND t1.Team IN (
  SELECT Team FROM t2 WHERE t3.ID = t2.ID 
  UNION 
  SELECT t1.Team
)
    Login or Signup to reply.
  4. 0

    Here’s a nice trick to keep the query simple and good performance:

    AND IFNULL(
(
    SELECT  MAX(IIF(t1.Team = t2.Team, 1, 0))
    FROM    t2
    WHERE   t3.ID       =   t2.ID
), 1)       =   1

    explanation:

    this sub-query does the following:

    1. compare all rows in t2 to t1 by Team
    2. if any matching Team- will return 1, else 0
    3. if no rows at all in t2- will return NULL
    4. the IFNULL will transform result of NULL to 1
    5. so we expect the result of the sub-query to be 1

    This means 1 will be returned from sub-query in 2 cases:

    • if no rows are found in t2
    • or if a matching Team found in t2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search