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