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.

Don't use the deprecated and insecure `mysql_*`-functions. They have been deprecated since PHP 5.5 (in 2013) and were completely removed in PHP 7 (in 2015). Use MySQLi or PDO instead. — M. Eriksson, Dec 29 '17 at 21:22

score 1 · Accepted Answer · answered Dec 29 '17 at 21:41

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

answered Dec 29 '17 at 21:41

1

I'm very thankful to you all! First example works great and have simple syntax. Thank you! – Łukasz Mikowski Dec 29 '17 at 21:49
@ŁukaszMikowski - you're welcome, please accept the answer if you found it helpful. – Dec 29 '17 at 21:50
I have one more question. What if I what to select more than 1 column? – Łukasz Mikowski Dec 29 '17 at 22:07
@ŁukaszMikowski ,to select extra columns, you can list extra columns in the select. – Dec 29 '17 at 22:08
@ŁukaszMikowski - instead of `SELECT m.ID`, you can `SELECT m.*` – Dec 29 '17 at 22:17

score 0 · Answer 2 · answered Dec 29 '17 at 21:23

0

MySQL doesn't support EXCEPT, use WHERE NOT instead

answered Dec 29 '17 at 21:23

Mark Baker

209,507
32
346
385

Why EXCEPT function doesn't work?

2 Answers2