mysql - Is “Where IN” with multiple columns defined in Standard SQL?

Question

I'm working on a query like this:

SELECT * FROM requests where (id,langid) IN (SELECT nid,langid FROM node)

My questions are

does this work in mysql and postgresql ? is this something supported by Standard SQL ?

I know is not the best solution and a JOIN would work but I'm not interested in that.

Answer 1

Standard and portable SQL would be EXISTS.. and is semantically the same IN

SELECT *
FROM requests R
WHERE 
    EXISTS (SELECT *
           FROM node n
           WHERE r.id = n.nid AND r.langid = n.langid
           )

The multi-column IN isn't portable to SQL Server or Sybase at least.

Other notes:

• A JOIN may require a DISTINCT and is not the same as IN or EXISTS.
• The final option is INTERSECT which is less commonly supported and works like IN/EXISTS
• IIRC some prehistoric MySQL versions (3.x?) didn't support the correlation for EXISTS