CODEWITHSUNDEEP
mysqlsqlsql-server
Mainak
Mainak

Reputation: 470

What is the difference between the following sql queries?

I have a table say Table1 which has column1,column2.column3

now I run a select query :

Select * from Table1

which returns all the rows.

Another query 

Select * from Table1 where column1 like '%%'

also returns all the rows. The first one is a simple regular sql select query. Why does the second one act similar to the first one? Can anyone tell me?

Upvotes: 1

Views: 79

Answers (3)

Shankar
Shankar

Reputation: 866

  • Because column1 like %% didn't filter any rows in the data set of the table.

  • To be brief % will match any number of characters in Column1.

  • If you want to match % character you need to use escaped patterns \%

https://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23361

The difference is only that in second one you have a filter that is not filtering anything.

The LIKE operator works by searching a text or part of a text from a field. The % is a wildcard that is used to replace a set of characters (any amount).

In your case the second query is returning all rows because your given filter column1 like '%%' is evaluating to everything.

It works like this.

Imagine that your field column1 has these values

foo
bar
goo
boo
zar

So if you use column1 like '%%' you are saying to your database: give me all rows that the text into that column1 starts with anything (any times) and ends with anything. So everything. It will also return all rows if you use column1 like '%'

But if you use column1 like '%oo%' it will give you as answer foo, goo and boo or if you use column1 like '%r' you will have bar and zar

Hope it helps you to understand.

Upvotes: 1

Michael Fourre
Michael Fourre

Reputation: 3015

What are you expecting the outcome to be? '%' is a wildcard in SQL, so you're basically saying this:

1) "Select all from table" 2) "Select all from table where column 1 is literally anything"

These are effectively the same since the latter part of the second query does not refine the conditions at all. It's kind of like saying:

1) "Select all from alphabet" 2) "Select all from alphabet where the entry is a letter"

It's going to produce the same results because you aren't adding a significantly refined condition to the statement.

Upvotes: 0

Related Questions