Can I use less values in IN clause in SQL than the subquery contains?

For example:

Select...
FROM...
WHERE street IN (
    SELECT street, country
    FROM ...
    WHERE ...
);

Here we have only 1 value before IN (that is "street") and the subquery have "street" and "country". My question is, can I do that kind of thing?

I have a side question:
Can I use MAX(…) inside WHERE clause?
Can I use data that are not in SELECT clause? Like:

SELECT data.person, data.city

FROM data

WHERE MAX(data.number)

GROUP BY city

We have person, city as a candidate key. Than the output of the query would be the city with its respective person that have the highest number.

>Solution :

The syntax you provided is not valid SQL because the subquery used in the IN clause is selecting two columns (street and country). The IN clause expects a subquery to return a single column of values.

If you want to filter rows from the main table for example (addresses) based on the combination of street and country from the subquery (valid_streets), you should use the EXISTS or JOIN clause. Here’s an example using EXISTS:

SELECT *
FROM addresses a
WHERE EXISTS (
    SELECT 1
    FROM valid_streets v
    WHERE v.street = a.street
      AND v.country = a.country
      AND condition
);

SELECT a.*
FROM addresses a
JOIN valid_streets v ON a.street = v.street AND a.country = v.country
WHERE condition;

I hope it will help you to understand, shortly explain, you cannot search or filter one dimension array into two columns