CODEWITHSUNDEEP
mysql
Martin de Ruiter
Martin de Ruiter

Reputation: 525

Why does MySQL IN() function work with unknown field

Having two tables:

Customers with fields CustomerId and AnotherId

Orders with fields OrderId and CustomerId

How is it possible that the following query works, even though AnotherId does not exist in the Orders table?

SELECT * 
FROM Customers 
WHERE (CustomerID, AnotherID) IN (SELECT CustomerID, AnotherID FROM Orders);

Upvotes: 0

Views: 35

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522302

It appears that MySQL is interpreting the AnotherId column reference in the subquery as belonging to the Customers table.

To confirm this, consider the following version of your query which does not work:

SELECT * 
FROM Customers 
WHERE (CustomerID, AnotherID) IN (SELECT o.CustomerID, o.AnotherID FROM Orders o);

In this case, it is clear that the subquery is referring to only columns from the Orders table, and, since that table has no AnotherId column, the query fails. Here is a demo showing that this version of your query fails:

Demo

Upvotes: 2

Related Questions