There are at least a few possible reasons you might be getting the dreaded error 1064 (42000) that reads “You have an error in your SQL syntax…” etc when trying to use the MINUS operator in MariaDB.

I’ll explore these below and offer some solutions.

Example of the Error

Here’s an example of code that causes the error:

SELECT TeacherName FROM Teachers
MINUS
SELECT StudentName FROM Students;

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT StudentName FROM Students' at line 3

Normally, this error is produced when you have an error in your code. As it explicitly states “You have an error in your SQL syntax”.

However, that is only one of several possibilities here.

In my case, there’s no error in my syntax. There’s got to be another reason.

Below are some possible reasons for this error, and suggestions for how to fix each one.

Solution 1

First of all, the MINUS operator was introduced in MariaDB 10.6.1. Therefore, if you’re running an earlier version of MariaDB, the MINUS operator won’t work for you.

In this case, upgrade your MariaDB installation to the latest version (or at least, a version no earlier than 10.6.1).

Solution 2

Secondly, the MINUS operator only works in Oracle mode. Although this isn’t explicitly mentioned in MariaDB’s documentation, it is implied in the task for implementing the MINUS operator in MariaDB.

If you’re already running MariaDB 10.6.1 or later, you’ll need to switch to Oracle mode before you use the MINUS operator.

Therefore, run the following code before you start using the MINUS operator:

SET sql_mode = "oracle";

Now you should be able to use the MINUS operator in your code.

Solution 3

If you’ve done all of the above, but you’re still getting an error, it’s possible that there really is an error in your syntax.

For example, I’m running MariaDB 10.6.1 in Oracle mode, but the following code produces the error:

SELECT TeacherName FROM Teachers;
MINUS
SELECT StudentName FROM Students;

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MINUS

In this case, I accidentally included a semi-colon after the first SELECT statement, which is a common mistake. To fix this, I’d simply remove that first semi-colon:

SELECT TeacherName FROM Teachers
MINUS
SELECT StudentName FROM Students;

Result:

+-------------+
| TeacherName |
+-------------+
| Ben         |
| Cathy       |
+-------------+

Success!

Solution 4

If you’ve tried all of the above solutions, but you’re still getting an error, maybe it’s time to use the EXCEPT operator. The MINUS operator is a synonym for EXCEPT, so they both use the same syntax and return the same result.

SELECT TeacherName FROM Teachers
EXCEPT
SELECT StudentName FROM Students;

Result:

+-------------+
| TeacherName |
+-------------+
| Ben         |
| Cathy       |
+-------------+

Solution 5

If by now you don’t want anything to do with MINUS or EXCEPT, there is another option.

You can use a subquery like the following to return the same result:

SELECT 
    DISTINCT TeacherName
FROM Teachers t 
WHERE NOT EXISTS (SELECT StudentName FROM Students s
WHERE t.TeacherName = s.StudentName);

Obviously, the exact query you use will depend on your schema and data.