Search code examples
mysqlsqlsubquerywhere-clause

MySQL multiple columns in ALL clause

Why it is not allowed to do the following?

SELECT Column_1
FROM Mytable
WHERE (Column_1, Column_2) >= ALL(SELECT Column_1,Column_2 FROM MYTABLE2)

But it is possible to do

SELECT Column_1
FROM Mytable
WHERE (Column_1, Column_2) IN (SELECT Column_1,Column_2 FROM MYTABLE2)
Solution

  • Assuming that this is the query you wanted to write:

    select col1 from t1 where (col1, col2) > all(select col1, col2 from t2);

    This does not work in MySQL, and raises error:

    Operand should contain 1 column(s)

    MySQL supports tuple (in)equality though, so you could phrase this with not exists:

    select col1 
from t1
where not exists (
    select 1
    from t2
    where (t2.col1, t2.col2) > (t1.col1, t1.col2)
)