Why use a WHERE 1 = 1 or WHERE 1 clause?

In this article, we will study some cases where a clause WHERE 1 = 1 or WHERE 1 is useful.

Some database engines such as MySQL show us a WHERE 1 = 1 or WHERE 1 clause when we inspect the content of a table or when we use a template to build a query.

Image 1. MySQL query template with WHERE 1 clause

But, have you ever wondered why? In what case is that type of clause useful? Below we will see a couple of examples where this type of clause is useful.

To build the examples, we will use an Employee table with the following structure and data:

CREATE TABLE `employee` (   `id` int(11) NOT NULL,   `birth_date` date NOT NULL,   `first_name` varchar(20) NOT NULL,   `last_name` varchar(20) NOT NULL,   `gender` varchar(10) NOT NULL,   `hire_date` date NOT NULL ) ; INSERT INTO `employee` (`id`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10001, '1963-09-02', 'Dorris', 'Bodhi', 'M', '1996-06-26'), (10002, '1974-06-02', 'Bryanna', 'Kolleen', 'F', '1995-11-21'), (10003, '1969-12-03', 'Rayner', 'Colt', 'M', '1996-08-28'), (10004, '1964-05-01', 'Tanner', 'Clifford', 'M', '1996-12-01'), (10005, '1965-01-21', 'Trent', 'Dolph', 'M', '1999-09-12'), (10006, '1963-04-20', 'Ashlee', 'Cristen', 'F', '1999-06-02'), (10007, '1967-05-23', 'Irene', 'Yazmin', 'F', '1999-02-10'), (10008, '1968-02-19', 'Terence', 'Dalton', 'M', '2004-09-15'), (10009, '1962-04-19', 'Regina', 'Wayland', 'F', '1995-02-18'), (10010, '1973-06-01', 'Shanon', 'Mortimer', 'F', '1999-08-24');

Script 1. Employee table creation

We can execute the following query to verify the Employee table content:

SELECT * FROM `employee`

Script 2. Query to retrieve Employee table rows

Result:

Table 1. Content of the Employee table 

WHERE 1 = 1 clause first example

For our first example, we are going to create a stored procedure that returns the data of an employee. To locate the employee, the stored procedure will receive two parameters.

CREATE DEFINER=`root`@`localhost` PROCEDURE `search_employee`(emp_id int, emp_gender varchar(10)) BEGIN DECLARE V_WHERE VARCHAR(100); DECLARE V_QUERY VARCHAR(200); SET V_WHERE = 'WHERE 1 = 1'; IF (emp_id != '') THEN SET V_WHERE = concat(V_WHERE, ' AND id = ', emp_id); END IF; IF (emp_gender != '') THEN SET V_WHERE = concat(V_WHERE, ' AND gender = ', QUOTE(emp_gender)); END IF; set @V_QUERY = CONCAT('SELECT id, first_name, last_name, gender, hire_date FROM employee', ' ', V_WHERE); PREPARE stmt FROM @V_QUERY; EXECUTE stmt; END

Script 3. WHERE 1 = 1 clause first example

In this example, we see how using the WHERE 1 = 1 clause, we avoid having to ask for each parameter if it is the first to place the word WHERE. We simply concatenate each parameter with an AND, since we always start the WHERE clause with the comparison 1 = 1.

We can execute the following query to retrieve the employee 10001 data:

CALL `employees_database`.`search_employee`(10001,'M');

Script 4. Call to the procedure to obtain employee 10001 data

Result:

Table 2. Employee 10001 data

WHERE 1 = 1 clause second example

In our second example, we will build a procedure that will filter employees by gender. We will use a parameter to indicate the gender to be filtered. If the parameter is empty, it will bring all the employees.

CREATE DEFINER=`root`@`localhost` PROCEDURE `search_employee_by_gender`(emp_gender varchar(10)) BEGIN DECLARE V_WHERE VARCHAR(100); DECLARE V_QUERY VARCHAR(200);    SET V_WHERE = 'WHERE 1 = 1'; IF (emp_gender = 'M') or (emp_gender = 'Male') THEN SET V_WHERE = concat(V_WHERE, ' AND gender = ', QUOTE('M')); END IF;         IF (emp_gender = 'F') or (emp_gender = 'Female') THEN SET V_WHERE = concat(V_WHERE, ' AND gender = ', QUOTE('F')); END IF; set @V_QUERY = CONCAT('SELECT id, first_name, last_name, gender, hire_date FROM employee', ' ', V_WHERE); PREPARE stmt FROM @V_QUERY;     EXECUTE stmt; END

Script 5. WHERE 1 = 1 clause second example

We can execute the following query if we want to filter the female employees:

CALL `employees_database`.`search_employee_by_gender`('Female');

Result:

Table 3. Female employees

Mysql: INNER JOIN ON vs WHERE clause

For simplicity, assume all relevant fields are NOT NULL.

You can do:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1, table2
WHERE
    table1.foreignkey = table2.primarykey
    AND (some other conditions)

Or else:

SELECT
    table1.this, table2.that, table2.somethingelse
FROM
    table1 INNER JOIN table2
    ON table1.foreignkey = table2.primarykey
WHERE
    (some other conditions)

Do these two work on the same way in MySQL?

 Answers

INNER JOIN is ANSI syntax which you should use.

It is generally considered more readable, especially when you join lots of tables.

It can also be easily replaced with an OUTER JOIN whenever a need arises.

The WHERE syntax is more relational model oriented.

A result of two tables JOINed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

It's easier to see this with the WHERE syntax.

As for your example, in MySQL (and in SQL generally) these two queries are synonyms.

Also note that MySQL also has a STRAIGHT_JOIN clause.

Using this clause, you can control the JOIN order: which table is scanned in the outer loop and which one is in the inner loop.

You cannot control this in MySQL using WHERE syntax.

---

Applying conditional statements in ON / WHERE

Here I have explained about the logical query processing steps.

---

Reference : Inside Microsoft® SQL Server™ 2005 T-SQL Querying 
Publisher: Microsoft Press 
Pub Date: March 07, 2006 
Print ISBN-10: 0-7356-2313-9 
Print ISBN-13: 978-0-7356-2313-2 
Pages: 640 

Inside Microsoft® SQL Server™ 2005 T-SQL Querying

(8)  SELECT (9) DISTINCT (11) TOP <top_specification> <select_list>
(1)  FROM <left_table>
(3)       <join_type> JOIN <right_table>
(2)       ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

The first noticeable aspect of SQL that is different than other programming languages is the order in which the code is processed. In most programming languages, the code is processed in the order in which it is written. In SQL, the first clause that is processed is the FROM clause, while the SELECT clause, which appears first, is processed almost last.

Each step generates a virtual table that is used as the input to the following step. These virtual tables are not available to the caller (client application or outer query). Only the table generated by the final step is returned to the caller. If a certain clause is not specified in a query, the corresponding step is simply skipped.

Brief Description of Logical Query Processing Phases

Don't worry too much if the description of the steps doesn't seem to make much sense for now. These are provided as a reference. Sections that come after the scenario example will cover the steps in much more detail.

1. FROM: A Cartesian product (cross join) is performed between the first two tables in the FROM clause, and as a result, virtual table VT1 is generated.
2. ON: The ON filter is applied to VT1. Only rows for which the <join_condition> is TRUE are inserted to VT2.
3. OUTER (join): If an OUTER JOIN is specified (as opposed to a CROSS JOIN or an INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT2 as outer rows, generating VT3. If more than two tables appear in the FROM clause, steps 1 through 3 are applied repeatedly between the result of the last join and the next table in the FROM clause until all tables are processed.
4. WHERE: The WHERE filter is applied to VT3. Only rows for which the <where_condition> is TRUE are inserted to VT4.
5. GROUP BY: The rows from VT4 are arranged in groups based on the column list specified in the GROUP BY clause. VT5 is generated.
6. CUBE | ROLLUP: Supergroups (groups of groups) are added to the rows from VT5, generating VT6.
7. HAVING: The HAVING filter is applied to VT6. Only groups for which the <having_condition> is TRUE are inserted to VT7.
8. SELECT: The SELECT list is processed, generating VT8.
9. DISTINCT: Duplicate rows are removed from VT8. VT9 is generated.
10. ORDER BY: The rows from VT9 are sorted according to the column list specified in the ORDER BY clause. A cursor is generated (VC10).
11. TOP: The specified number or percentage of rows is selected from the beginning of VC10. Table VT11 is generated and returned to the caller.

---

Therefore, (INNER JOIN) ON will filter the data (the data count of VT will be reduced here itself) before applying WHERE clause. The subsequent join conditions will be executed with filtered data which improves performance. After that only the WHERE condition will apply filter conditions.

(Applying conditional statements in ON / WHERE will not make much difference in few cases. This depends how many tables you have joined and number of rows available in each join tables)

---

Implicit joins (which is what your first query is known as) become much much more confusing, hard to read, and hard to maintain once you need to start adding more tables to your query. Imagine doing that same query and type of join on four or five different tables ... it's a nightmare.

Using an explicit join (your second example) is much more readable and easy to maintain.

---

They have a different human-readable meaning.

However, depending on the query optimizer, they may have the same meaning to the machine.

You should always code to be readable.

That is to say, if this is a built-in relationship, use the explicit join. if you are matching on weakly related data, use the where clause.

---

I know you're talking about MySQL, but anyway: In Oracle 9 explicit joins and implicit joins would generate different execution plans. AFAIK that has been solved in Oracle 10+: there's no such difference anymore.

MySQL: Using IF in a WHERE clause

I recently needed to use an IF statment in a WHERE clause with MySQL. This isn't the most ideal situation and should probably be avoided normally but we needed to do it for one reason or another and this post shows how to do it.

How IF works

If works like this:

IF(<condition>, <value if true>, <value if false>)

So as an example, the first query below would return 1 and the second 0:

SELECT IF( 'a' = 'a', 1, 0 );
SELECT IF( 'a' = 'b', 1, 0 );

Using IF in a WHERE query

The following example shows how to use IF in a WHERE query.

SELECT ...
WHERE ...
AND IF(myfield = 'somevalue', 1, 0) = 1

In the above example, if the value from the column "myfield" matches "somevalue" then the IF function will evaluate to 1. This is then compared with the value 1 which returns true or false for this part of the where condition depending whether the IF function returns 1 or 0.

This simple example would be obviously be better represented like this and would be more efficient because it's not using an IF function:

SELECT ...
WHERE ...
AND myfield = 'somevalue'

Our actual code had a nested IF which simplified the original query and looked something like this:

SELECT ...
WHERE ...
AND IF(myfield = 'somevalue', IF(otherfield = 12345, 1, 0), 0) = 1

An alternative to the above without using IF functions would look something like this:

SELECT ...
WHERE ...
AND ( (myfield = 'somevalue' AND otherfield = 12345) OR myfield != 'somevalue' )

But should you use IF in a WHERE clause?

Probably not. It's probably not the most efficient way of doing things. Ideally you should have already filtered with other WHERE statements otherwise it will have to do the IF function on every row in the database.

I've posted this mainly as a reference to show how it can be done. I'll leave whether or not it should be done up to you :)

 

Related posts:

• Selecting substrings with MySQL using LOCATE and SUBSTRING
• MySQL: Delete records in one table that are not in another
• MySQL: Find records in one table that are not in another
• String concatenation with MySQL
• Insert multiple records into MySQL with a single query

mysql where statement does not display the appropriate results

I have a database that has 2 records in it:

id |  message  |    date     |   user    | option
---+-----------+-------------+-----------+--------
1  |  Welcome  |  2015-03-01 |           |   0
2  |  message  |  2015-03-05 |  admin    |   0

What I'm trying to do is if the user field is blank, it shows the message to all users, if there is a username (such as admin in this example) listed, it shows the one for that user, and a the blank message if it exist.

Right now it will show both messages if the user is Pam (it should only show id 1).

If the user is admin, it shows both messages.

It seems like its ignoring the user = '$zuser'

What am I doing wrong?

<?php 

  ini_set('display_errors',1);  error_reporting(E_ALL);
  $zuser=$_COOKIE['aauser'];

  $result = mysqli_query($con,"SELECT * FROM message WHERE (user = '$zuser' OR run_date >= CURDATE() AND user='')");

  while($quick = mysqli_fetch_array($result))
  {
    echo $quick['message'];
    echo '<script>alert("'.$quick['message'].'");</script>';
  }

?>

---

Found my problem @ Fred -ii- that was a bone head mistake i forgot that cookies were only set after the second page loaded!!!

Thank you for your help!

<?php
        ini_set('display_errors',1);  error_reporting(E_ALL);
        echo $zuser=$_GET['usrname'];

    $result = mysqli_query($con,"SELECT * FROM message WHERE user = '$zuser' OR run_date >= CURDATE() AND user=''");

                while($quick = mysqli_fetch_array($result)) 

                {
                echo $quick['message'];
                echo '<script>alert("'.$quick['message'].'");</script>';
                }

        ?>




The Mysql query does not work with WHERE

I was trying to get some data from my database, however I am currently only getting errors.

The query I am trying to do =

SELECT
   count(id),
   day(created_at),
   year(created_at),
   month(created_at)
FROM
   `orders`
WHERE
   day(created_at) = BETWEEN 1 AND 7 month(created_at) = 6
   AND year(created_at) = 2014
   AND company_id = 1
group by
   year(created_at),
   month(created_at),
   day(created_at)

The days between 1 and 7 will be the days sunday trough saturday to get all orders in that week.

Thanks in advance.

---

   You have SQL Syntax errors , MISSING "AND" and "BETWEEN" not used correctly.
   Try the following:-

    SELECT COUNT(id), day(created_at), year(created_at), month(created_at)
    FROM
   `orders`
    WHERE day(created_at)  BETWEEN 1 AND 7
    AND  month(created_at) = 6
    AND year(created_at) = 2014
    AND company_id = 1
    GROUP BY year(created_at), month(created_at), day(created_at);




Why MySQL does not use an index in the WHERE IF clause?

Because of this setting:

mysql> show global variables like '%indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

The slow queries log keep receiving:

# Time: 120607 16:58:30
# User@Host: xbtit[xbtit] @  [123.30.53.244]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 16006
SELECT * FROM xbtit_files WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';

Trying to explain this query:

mysql> EXPLAIN SELECT * FROM xbtit_files WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | xbtit_files | ALL  | NULL          | NULL | NULL    | NULL | 16006 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+

What surprised me is why MySQL not using indexes:

mysql> show index from xbtit_files;
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| xbtit_files |          0 | PRIMARY   |            1 | info_hash   | A         |       16006 |     NULL | NULL   |      | BTREE      |         |
| xbtit_files |          1 | filename  |            1 | filename    | A         |       16006 |     NULL | NULL   | YES  | BTREE      |         |
| xbtit_files |          1 | category  |            1 | category    | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| xbtit_files |          1 | uploader  |            1 | uploader    | A         |          16 |     NULL | NULL   |      | BTREE      |         |
| xbtit_files |          1 | bin_hash  |            1 | bin_hash    | A         |       16006 |       20 | NULL   |      | BTREE      |         |
| xbtit_files |          1 | ix_sohaid |            1 | soha_id     | A         |       16006 |     NULL | NULL   | YES  | BTREE      |         |
+-------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

FORCE INDEX also doesn't work:

mysql> EXPLAIN SELECT * FROM xbtit_files force index (PRIMARY) WHERE IF(soha_id is null OR soha_id = '', info_hash, soha_id)='6d63dd4ab199190b531752067414d4d6e6568f90';
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | xbtit_files | ALL  | NULL          | NULL | NULL    | NULL | 16006 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+-------+-------------+

Must I split this query into 2 operations?

---

In MySQL, you cannot create indexes on expressions, and the optimizer is not smart enough to split your query against two indexes.

Use this:

SELECT  *
FROM    xbtit_files
WHERE   soha_id = '6d63dd4ab199190b531752067414d4d6e6568f90'
UNION ALL
SELECT  *
FROM    xbtit_files
WHERE   soha_id = ''
        AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90'
UNION ALL
SELECT  *
FROM    xbtit_files
WHERE   soha_id IS NULL
        AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90'

Each query uses its own index.

You can just combine it into a single query:

SELECT  *
FROM    xbtit_files
WHERE   (
        soha_id = '6d63dd4ab199190b531752067414d4d6e6568f90'
        OR
        (soha_id = '' AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90')
        OR
        (soha_id IS NULL AND info_hash = '6d63dd4ab199190b531752067414d4d6e6568f90')
        )

and create a composit index on (soha_id, info_hash) for this to work fast.

MySQL is also able to merge results from two indexes together, using index_merge, so there is a chance you would see this in the plan for the second query even if you don't create a composite index.

Mysql - IN () Function does not return results using a percent sign

It's short and simple.

I'm using this query:

SELECT * FROM TABLE_NAME WHERE COL_NAME IN('%VALUE1%', '%VALUE2%')

However it doesn't seem to work, I've used LIKE for both values combined using ORand it returns results, so I'm certain that there's rows in the table with these values.. am I missing something here?

Does MYSQL IN() function accept percentage sign? What is the right way to do it?

Thanks in advance!

---

You could try with RLIKE:

SELECT * FROM TABLE_NAME WHERE COL_NAME RLIKE 'VALUE[12]'

Keep in mind these sorts of queries tend to perform very badly because they require a full table scan. If you're doing this a lot you may want to adjust your schema to better represent your usage patterns.