Is it possible to emulate the WITH command in MySQL 5.1.73? I have the following query:

SELECT
    COUNT(*) AS `count`
FROM
    agreements
WHERE
    start_date &gt;= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    start_date &lt; STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    active = 1
    AND
    (driv_id, veh_id) NOT IN (
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) &lt;
                DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
    )


I then have to run the same query, having made a single change - removing the NOT from the (driv_id, veh_id) NOT IN ... part of the WHERE clause.

This is obviously not good because it means I literally copy and paste the query minus one word which violates DRY.

I googled around and discovered the WITH command, but it's not available in MySQL 5.1.73. The query I would have executed using WITH was:

WITH tuples AS
(
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) &lt; 
                DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
)
SELECT
    SUM(IF((a.driv_id, a.veh_id) IN `tuples`, 1, 0)) as in_sum
    SUM(IF((a.driv_id, a.veh_id) NOT IN `tuples`, 1, 0)) as not_in_sum
FROM
    agreements
WHERE
    start_date &gt;= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    start_date &lt; STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    active = 1


I tried doing this with a temporary table as well by storing the result of the subquery in a temp table called tuples and selecting (in my main query):

SUM(IF((a.driv_id, a.veh_id) IN tuples, 1, 0)) as in_sum
SUM(IF((a.driv_id, a.veh_id) NOT IN tuples, 1, 0)) as not_in_sum


But this would throw a syntax error (not quite sure why).

Is there a way to do this, perhaps using joins (I tried to work out a join query but didn't have any breakthroughs)?
Yes, it is possible to return both results without repeating the subquery by rewriting your query using an outer join. The following will return same results as your version:

SELECT
    COUNT(*) AS `count`
FROM
    agreements AS ag
LEFT JOIN
    (
        SELECT
            a.driv_id, a.veh_id
        FROM
            agreements a
        INNER JOIN
            types t
            ON
            a.agreement_type_id = t.id
        WHERE
            DATE_ADD(a.start_date,INTERVAL t.initial_term YEAR) &lt; DATE_SUB(STR_TO_DATE('14-01-2015','%d-%m-%Y'),INTERVAL 2 MONTH)
    ) AS s ON ag.driv_id = s.driv_id AND ag.veh_id = s.veh_id
WHERE
    ag.start_date &gt;= STR_TO_DATE('14-01-2015','%d-%m-%Y')
    AND
    ag.start_date &lt; STR_TO_DATE('15-01-2015','%d-%m-%Y')
    AND
    ag.active = 1
    AND
    s.driv_id IS NULL
;


That would effectively be your not_in_sum. In order to return both counts at once, you just remove the NULL check from the where clause and use COUNT(s.driv_id) in SELECT like this:

SELECT
    COUNT(s.driv_id) AS in_sum,
    COUNT(*) - COUNT(s.driv_id) AS not_in_sum
FROM
    ...


If the subquery can return duplicates, add DISTINCT to it, otherwise in_sum may return an incorrect result.

As for the WITH clause, there is no mention of it even in the manual for the not yet officially released version 5.7, but using a temporary table to emulate it would probably make sense. The error you got while trying that method was because the right side of IN/NOT IN in your attempts was just a table name whereas it should have been a subquery:

(a.driv_id, a.veh_id) IN (SELECT driv_id, veh_id FROM tuples)