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

Answer 2

I checked that with PostgreSQL and it works (it is officially supported), but it's your responsibility to make id ↔ nid and langid ↔ langid column types compatible (or use explicit casting).

I think it is pretty standard construct. I have SQL:2003 draft and there is in predicate defined (as well as mentioned exists predicate).

8.4 <in predicate>

Function

Specify a quantified comparison.

Format

<in predicate> ::= <row value predicand> <in predicate part 2>
<in predicate part 2> ::= [ NOT ] IN <in predicate value>
<in predicate value> ::=
<table subquery>
| ... (rest is not important here)

EDIT:

As checked works well under MySQL too (version 5.0.90-log). Here is documentation link.