How to Handle Missing Records in an IN Query with Default Values?

Retrieve default value for IN query condition

Suppose a query retrieves user information and the number of minutes they were online within a specified date range. It uses an IN query to filter users based on their ID. The goal is to return a default value for those IDs that don't have any matching records.

Simplified query:

<code class="language-sql">SELECT users.Name, users.ID, SUM(users.Minutes) AS MinutesOnline
FROM UserTable
     LEFT JOIN OnlineUseage ON OnlineUseage.ID = UserTable.ID
WHERE OnlineUseage.Date >= '2016-01-01 00:00:00' AND OnlineUseage.Date < '2016-01-31 00:00:00'
AND users.ID IN (1,2,3,4,5);</code>

Modified query:

To achieve the desired results, move the condition for OnlineUseage to the FROM clause. This ensures that the left join behaves like an inner join, filtering out users without any matching records in OnlineUseage.

<code class="language-sql">SELECT
    users.Name,
    users.ID,
    IFNULL(SUM(users.Minutes), 0) AS MinutesOnline
FROM
    users
    LEFT JOIN OnlineUseage ON
        OnlineUseage.ID = users.ID AND
        OnlineUseage.Date >= '2016-01-01 00:00:00' AND
        OnlineUseage.Date < '2016-01-31 00:00:00'
WHERE users.ID IN (1,2,3,4,5);</code>

The query will now retrieve users with matching records in OnlineUseage and users without matching records, while setting MinutesOnline to the default value of 0 for those users without matching records.

The above is the detailed content of How to Handle Missing Records in an IN Query with Default Values?. For more information, please follow other related articles on the PHP Chinese website!