Home / Questions / 39586
Accepted
Hoose
Asked: 2020-12-17 07:30:51 +0800 CST 2020-12-17 07:30:51 +0800 CST

Syntax 1=1 in SQL WHERE

  • 772

What 'meaning' does this SQL statement have in the WHERE clause?

SELECT * FROM cat_precios WHERE 1=1 ORDER BY PRE_ID

Specifically:WHERE 1=1

I've never really seen anything like it and I have no idea how it works. Could you explain to me what you evaluate there?

As a note, there is no field named 1

mysql

5 Answers

  1. Best Answer

    In the query it means if True, that is, it is superfluous because it will always be true and it is an apparently useless condition in that case...

    But normally it is used when you are going to create a WHEREsystem where you don't know the exact conditions, but they are put together on the fly, so all the other conditions that are added will always carry AND.

    Example:

    You have a screen with several FILTERING options:

    • By name
    • By date
    • by id

    and they are all optional, so your query can be:

    select * from tabla where

    But you don't know what filter options it will have, it can be all or none or some, so a generic query is created:

    select * from tabla where 1 = 1

    that works for all cases and according to the options the conditions are added:

     - and nombre = 'nombre'
 - and fecha = '2016-01-01'
 - and id = 10

    then query could be built:

    select * from tabla where 1 = 1 and id = 10

    leaving the filter options dynamic, otherwise we would not be able to know if the condition should carry andor not or if whereit would have to go in the query.

    • 21

  2. I have the idea that it is mostly used when the number of conditions that the query will have is not known at compile time; but these are generated even at runtime. When you already have the WHERE 1=1solo, the other conditions are concatenated to it.

    It is the same as WHERE = 1or WHERE = true.

    • 3

  3. the where statement is used to filter the rows that are selected (that are going to be displayed) from the data set, when you put where true as in this case (since 1 = 1 would technically give true) it means that there are no restrictions and all the records will be displayed is the same as if the query were:

    SELECT * FROM cat_precios ORDER BY PRE_ID

    without the where.

    It is also used to build queries where 0 or more restrictions are going to be added to the where, since where must have at least one condition.

    • 1
    1. similar to this
    2. 1=1? Claro is simply a condition as a programmer whose result is boolean "True"
    • 0

  5. An alternative solution is to control the chain of conditions. If it is empty, WHERE is added, if it already contains something, AND is added. We then concatenate that string to our sql statement. The result is a bit cleaner than WHERE 1 = 1 but it also requires a bit more work :-)

    dim sWhere as string = String.Empty

    For each condition in conditions

        If sWhere = String.Empty then

            sWhere = "WHERE "

        else

            sWhere = sWhere & " AND "

        End If

        sWhere = sWhere & condition.ToString

    next condition

    • 0