User Variable Assignment from Query Result in MySQL

Background and Objective

Assigning user-defined variables based on query results enhances database manipulation capabilities. This article explores a method to achieve this in MySQL without resorting to nested queries.

User Variable Assignment Syntax

Contrary to popular belief, user variable assignment can be integrated directly into queries. The assignment operator for SET statements is either = or :=. However, := must be used within other statements, as = is reserved for comparisons.

Query Example

To illustrate this technique, let's assume USER and GROUP are unique. The following query sequence showcases the functionality:

SET @user := 123456;
SELECT @group := `group` FROM USER WHERE user = @user;
SELECT * FROM USER WHERE `group` = @group;

Sample Data and Results

Using a sample table with the following data:

CREATE TABLE user (`user` int, `group` int);
INSERT INTO user VALUES (123456, 5);
INSERT INTO user VALUES (111111, 5);

The query yields the following result:

 -------- ------- 
| user   | group |
 -------- ------- 
| 123456 |     5 |
| 111111 |     5 |
 -------- ------- 

Alternative Approach

As an alternative, the following syntax can be employed:

SET @user := 123456;
SELECT `group` FROM user LIMIT 1 INTO @group;
SELECT * FROM user WHERE `group` = @group;