Why EXCEPT function doesn't work?

Question

I have mysql query like this below:

$missingTypo = mysql_query("SELECT m.ID
                              FROM table1 AS m
                              LEFT JOIN table2 AS ut
                              ON ut.matchID = m.ID
                              WHERE data > '$now'
                          EXCEPT
                            SELECT m.ID
                              FROM table1 AS m
                              LEFT JOIN table2 AS ut
                              ON ut.matchID = m.ID
                              WHERE data > '$now' 
                              AND ut.typer = '$name'");

First state query returns 3 result [10, 11, 12], second return 1 result [10]. Outcome of whole query should be [11, 12] but it doesn't work. var_dump function return false. But, if I exchange "EXCEPT" to for example "UNION ALL" it works fine and gives [10, 11, 12, 10].

Please help.

Answer 1

As the other answer suggested,EXCEPT is not supported in Mysql, but you can use the NOT IN predicate:

SELECT m.ID
FROM table1 AS m
LEFT JOIN table2 AS ut ON ut.matchID = m.ID
WHERE data > '$now'
  AND m.ID NOT IN(SELECT m2.ID
                  FROM table1 AS m2
                  INNER JOIN table2 AS ut ON ut.matchID = m.ID
                  WHERE data > '$now' 
                    AND ut.typer = '$name');

Or LEFT JOIN:

SELECT t1.ID
FROM
(
  SELECT m.ID
  FROM table1 AS m
  LEFT JOIN table2 AS ut ON ut.matchID = m.ID
  WHERE data > '$now'
) AS t1
LEFT JOIN
(
  SELECT m.ID
  FROM table1 AS m
  LEFT JOIN table2 AS ut ON ut.matchID = m.ID
  WHERE data > '$now' 
    AND ut.typer = '$name'
) AS t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL