Why using 'IN" to filter records along with a subselect returns records that have, in certain columns, equal values

Asked

Viewed 64 times

2

I was studying and I came across this, I know IN is like the OR, but what I didn’t understand was this command:

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

The return is simply the table records Customers that have in their column the value equal to the column of the records of the table Suppliers.

It didn’t make any sense to me.

mysql sql
  • 1

    To try to understand why you say, "It didn’t make any sense to me." show an example of input data the result obtained and the expected result. Apparently what you report as a return is the expected behavior of the clause IN, but I may not have understood your question.

3 answers

2


The IN serves to verify that the expression is equal to any of the given values (see the documentation).

For example, supposing we have these tables:

customers
| id | country   |
|----|-----------|
| 1  | Brasil    |
| 2  | Argentina |
| 3  | Paraguai  |
| 4  | Uruguai   |
| 5  | Peru      |

suppliers
| id | country   |
|----|-----------|
| 6  | Peru      |
| 7  | Chile     |
| 8  | Brasil    |

If I do so:

select * from customers where country in ('Brasil', 'Chile', 'Peru');

He’ll check on the table customers all records where the column country has one of the values indicated ("Brazil", "Chile" or "Peru"). As "Brazil" and "Peru" have, but "Chile" does not, the query returns:

| id | country |
|----|---------|
| 1  | Brasil  |
| 5  | Peru    |

But instead of fixed values, we can also place a subquery inside the IN. Thus, the values returned by subquery will be used.

That is, the query below:

select country from suppliers;

Returns this data:

| country |
|---------|
| Peru    |
| Chile   |
| Brasil  |

So if I do:

select * from customers
where country in (select country from suppliers);

It would be the same as doing the first query above (select * from customers where country in ('Brasil', 'Chile', 'Peru')). The difference, of course, is that the first, with fixed values, will always use those specific values. Already the one that uses the subquery can bring different results, according to the table suppliers is modified (since it always uses the values the table has at the time the query is executed).

Without more context, we can not explain much about what it would mean, but by the names of the tables and columns, what we can infer is: the query is looking for clients (customers) countries where there are also suppliers (Suppliers). If the customer is in a country where there is no registered supplier (for example, the customer with id equal to 2, whose country is Argentina, and which has no record in the table suppliers), he is not returned.

To find out if this consultation makes sense or not, just having more context on the system.

Finally, the same result would be obtained with INNER JOIN:

select c.* from customers c inner join suppliers s on c.country = s.country;

Read more on the subject here, here and here.

  • obg. to be able to understand, it was a lack of my reasoning

2

trying to be didactic, the IN [EM] is used to filter according to the following array.. in the case:

(SELECT Country FROM Suppliers)

this search will return an array containing the list of countries, example:

[''Brazil, 'USA', 'British']

or an array containing the ID’s of the respective countries:

[1,2,3]

and then the search will be carried out according to the attribute Country is contained in that returned array..

in short, it would be like carrying out the following research;

pesquise todos os atributos
dos clientes
os quais seus respectivos países estão na seguinte lista de países (
  seleção de todos os países dos fornecedores
);

equivalent:

SELECT *
FROM Customers
WHERE Country IN (
  SELECT Country FROM Suppliers
);

and then only customers who live in the countries where there are suppliers will be returned..

0

This makes sense when applying some condition to subselect, for example filter only retail suppliers.

In which countries we have retail suppliers ?

  SELECT Country  
    FROM Customers
    WHERE Country IN (SELECT Country 
                      FROM Suppliers
                      where segment = 'retail');

This can be done via Join also always fitting an analysis of which the best solution.

I had published and deleted this answer because I judged incomplete but after the other answers she adds something.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.