What is the difference between LIKE, IN and BETWEEN in Mysql?

Asked

Viewed 15,459 times

15

I’m having doubts about the difference and use of tags LIKE, IN and BETWEEN in Mysql. When to use tags and example?

mysql sql

4 answers

16


Clause / opearador LIKE

Clause / operator is used LIKE when you want to search a character, using coringa that is to say %, if you want to scan with characters that start with the word A the correct is to do as I will show below. First note the position of coringa % yeah, it makes a difference.

Here you will find all the records that contain the letter A in the word. In the first alternative only with the joker % and in the second %_caracter_%

SELECT * FROM tabela WHERE descricao LIKE "%A%" 
SELECT * FROM tabela WHERE descricao LIKE "%_A_%"

Here you will find all the records that contain the letter A at the end of the word. In the first alternative only with the joker % and in the second %_caracter

SELECT * FROM tabela WHERE descricao LIKE "%A"
SELECT * FROM tabela WHERE descricao LIKE "%_A"

Here you will find all the records that contain the letter A at the beginning of the word. In the first alternative only with the joker % and in the second caracter_%

SELECT * FROM tabela WHERE descricao LIKE "A%"
SELECT * FROM tabela WHERE descricao LIKE "A_%"

Now let’s go to the clause BETWEEN

You must use the clause BETWEEN when you only want to catch a break from your SELECT, that is, you want to know the sales made between the day 10/11/2017 and 13/11/2017. After all the translation of BETWEEN would be Entre. I will indicate in the select below that I want to search the records between the day 10/11/2017 and 13/11/2017. Remembering that to seek an interval only 2 conditions should be indicated

Select  * FROM tabela WHERE vendas BETWEEN 10/11/2017 AND 13/11/2017

Now let’s go to the clause IN

The clause IN is used when we want to query a table filtering the value of one of its fields, from a list of possibilities. That is, if you wish to give one SELECT in a table where you have N records but want to filter them for only what you think necessary. See, that below I will indicate that I want to get the records of the people who own the ID = 1,2,5,10 only.

SELECT * from pessoas WHERE id IN (1,2,5,10)
  • 4

    It is worth remembering that _ is also a "joker" of the like Wildcard - corresponds to a character

  • Hello @Everson, could edit the answer including this detail, I find very valid.

  • 1

    I edited the reply @Rafaelweber, see if it was good and got clarity

5

Examples in the rray response:

LIKE: is used to do partial searches in text type fields (varhcar text etc) using wildcards % _.

SELECT * FROM tabela
WHERE campo LIKE 'abc'

SELECT * FROM tabela
WHERE campo LIKE '%abc%'

IN: Compare a value against a fixed set or even with a subquery.

SELECT * FROM tabela
WHERE campo IN ('abc','def')

SELECT * FROM tabela
WHERE campo IN (10,20,30)

SELECT * FROM tabela
WHERE campo IN (SELECT campo FROM tabela
    WHERE campo LIKE '%abc%')

BETWEEN: Compares a value between a range of exactly two values.

SELECT * FROM tabela
WHERE campo BETWEEN '01/01/2017' AND '01/10/2017'

SELECT * FROM tabela
WHERE campo BETWEEN 10 AND 20

3

3

Between:

Definition, used to return records based on a range of values. Property for numeric type or date fields, e.g..:

SELECT * FROM Funcionario WHERE Admissao BETWEEN '2017-09-01' AND '2017-10-01'

Sqlfiddle - See example working

IN and Not IN:

Definition, Used to return records whose values meet or do not meet a certain list.

SELECT * FROM Funcionario WHERE Admissao IN ('2017-11-12', '2017-09-12')

SELECT * FROM Funcionario WHERE Admissao NOT IN ('2017-11-12', '2017-09-12')

Sqlfiddle - See the example with IN working | Sqlfiddle - example with NOT IN working

Like and Not Like:

Definition, used to return records that contain or do not contain the given character string. It uses the % to control the form of research. Your search may vary in 3 ways:

Sqlfiddle - Values started with a certain character set.

SELECT * FROM Funcionario WHERE Pessoa like 'Cai%'

Sqlfiddle - Values ending with a certain character set.

SELECT * FROM Funcionario WHERE Pessoa like '%los'

Sqlfiddle - Values containing a certain character set.

SELECT * FROM Funcionario WHERE Pessoa like '%ai%'

Browser other questions tagged mysql sql

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