SQL - Union of Selects inside Where... In clauses

Question

(First time posting here, hope it's not a silly thing to post. Just read the code if you don't want to read the text, since it's a simple code structure question, I think)

So, today I had to make a query that allowed me to know which actors star in the same scenes as a specific actor (in the context of a movie database). I don't think the table details are important to specify (but if they are I can post them).

While writing my query, I realized that first I had to perform the Union of the result of two queries (that gave me the id of the script the scene is part of, and the order of the scene inside that script), and then select all the actors from those scenes. Scenes are divided into Action Parts and Speech Lines, and Actors are only connected to those and not directly to the scenes.

I already have an answer that works (I still need to do another union but that is simple), but I want to understand why does it work and why doesn't my first answer work. The only thing I did was remove parenthesis.

So this answer DOES NOT WORK

Select Distinct name
From Staff S
Inner join MChar_ActPart MCAP on S.stid=MCAP.aid
Where (sid, sord) in ((Select Distinct sid, sord
                       From MChar_SpLine MCSL
                       Inner join Staff S on MCSL.aid = S.stid
                       Where name = 'John Idle')                     
                      Union 
                      (Select Distinct sid, sord
                       From MChar_ActPart MCAP
                       Inner join Staff S on MCAP.aid = S.stid
                       Where name = 'John Idle'))
And name != 'John Idle';

I get this error. "SQL Error (1064): 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 'Union (Select Distinct sid, sordFrom MChar_ActPart MCAPInner join Staff S ' at line 9

But this one DOES WORK:

Select Distinct name
From Staff S
Inner join MChar_ActPart MCAP on S.stid=MCAP.aid
Where (sid, sord) in (Select Distinct sid, sord
                      From MChar_SpLine MCSL
                      Inner join Staff S on MCSL.aid = S.stid
                      Where name = 'John Idle'
                      Union 
                      Select Distinct sid, sord
                      From MChar_ActPart MCAP
                      Inner join Staff S on MCAP.aid = S.stid
                      Where name = 'John Idle')
And name != 'John Idle';

The only thing that is different are the parenthesis. Why does one work and the other not work?

Answer 1

The error you are seeing is related to a bug on MariaDB, when you are doing ((SELECT ...) UNION (SELECT ...)).

You can check the status of the bug on this link: https://jira.mariadb.org/browse/MDEV-10028

Note: adding this as an answer, as I think is the right answer to the specific error you were facing.

Answer 2

2*Ouch.

WHERE (a,b) ... is not well optimized; avoid it.

IN ( SELECT ... ) is often now well optimized; avoid it.

Use a 'derived subquery' to turn the query inside out, thereby avoiding both ouchies:

SELECT ...
    FROM ( ( SELECT sid, sord FROM ... )
           UNION ALL  -- or DISTICT
           ( SELECT sid, sord FROM ... )
         ) AS u
    JOIN ... AS x
        ON x.sid = u.sid
       AND x.sord = u.sord
    ...

Answer 3

The union and select distinct are redundant. I would strongly advise you to write the query as:

Select Distinct name
From Staff S Inner join
     MChar_ActPart MCAP 
     on S.stid = MCAP.aid
Where ((sid, sord) in (Select sid, sord
                       From MChar_SpLine MCSL Inner Join
                            Staff S
                            on MCSL.aid = S.stid
                       Where name = 'John Idle'
                      ) or
       (sid, sord) in (Select sid, sord
                       From MChar_ActPart MCAP Inner Join
                            Staff S
                            on MCAP.aid = S.stid
                       Where name = 'John Idle'
                      )
      ) and
      name <> 'John Idle';

There is much more scope to optimize this version.