SQL command to display specific records from the "id" field

Asked

Viewed 25,430 times

9

Which SQL command would display the records where the field id were 22, 23, 25 and 27?

exemplo de resultado

mysql sql

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

  • The IN is "cleaner" ...

5

  • Why not use AND?

  • AND means E, right? So when you use AND, you want both conditions to be true. If you change the OR for AND in 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 um ide 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 AND and OR. In the case of IN you 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 the IN in this if it is a data list. In other cases, only the OR will 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)

Show 4 more comments

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.