9
Which SQL command would display the records where the field id were 22, 23, 25 and 27?
3 answers
22
It would be something like:
SELECT * FROM tabela WHERE id IN (22,23,25,27);
The Operator IN:
The operator IN allows you to specify several values in a clause WHERE.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
Also works with strings:
SELECT * FROM Customers
WHERE City IN ('Paris','London');
Source: w3schools
5
Basically the same as your previous question. Only now uses the OR to select more than one, namely 22 OR 23 OR 25 OR 27, any of them will be selected.
SELECT * FROM tabela WHERE id = 22 OR id = 23 OR id = 25 OR id = 27;
-
Why not use AND?
-
ANDmeansE, right? So when you use AND, you want both conditions to be true. If you change theORforANDin this select, não retornaria nada. Porque qual linha teria o is 22 E 23 E 35 E 27. Nenhum! Para isto cada linha teria que ter mais de umide pelo modelo mostrado isto é impossível. Podemos interpretar oANDcomo excludente, enquanto que oOR`is inclusive, it is Somador, it adds everything that fits the conditions. -
Because the id would have to be 22 and 23 and ... impossible.
-
What about using IN(22,23,25,27);?
-
2
Also works, I preferred to use this form that is more basic because I understood that you are starting and need to understand the difference between
ANDandOR. In the case ofINyou are saying "I want it to be any line that is WITHIN this value range. In practice it gives the same result". When you get more experienced you will always prefer to use theINin this if it is a data list. In other cases, only theORwill solve. -
Is there anything wrong here to have negative?
-
IN(1,2,3,n...)is aesthetically more beautiful and simpler to type, but gives the same id = 1 or id=2 or id=3... -
You can do that too:
...where id IN(1,2,3) or id IN (4,5,6)and can also deny:...where id NOT IN(1,2,3) -
And if you want to order by unit, do something like this:
... order by FIELD(id, 22,23,25,27)
0
best way! will select the fields as you want
SELECT * FROM tabela WHERE id = 22 and id REGEXP '(23|25|27)';
Browser other questions tagged mysql sql
You are not signed in. Login or sign up in order to post.
The IN is "cleaner" ...
– Motta