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)

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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
)

CodePudding user response：

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