Practice Now
SQL
user variables
database query
programming
data management

Set user variable from result of query

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

Introduction

In MySQL, you can set a user-defined variable from a query result using SELECT ... INTO @variable or SET @variable = (SELECT ...). User variables are session-scoped (prefixed with @), persist until the session ends, and can be used in subsequent queries. This is useful for storing intermediate results, building dynamic queries, and avoiding repeated subqueries. PostgreSQL and SQL Server have different syntax for the same concept.

MySQL: SELECT ... INTO @variable

sql
1-- Set a single variable from a query
2SELECT MAX(salary) INTO @max_salary FROM employees;
3SELECT @max_salary;  -- 150000
4
5-- Set multiple variables
6SELECT MIN(salary), MAX(salary), AVG(salary)
7INTO @min_sal, @max_sal, @avg_sal
8FROM employees;
9
10SELECT @min_sal, @max_sal, @avg_sal;
11-- 30000, 150000, 75000

MySQL: SET @variable = (SELECT ...)

sql
1-- Using SET with a subquery
2SET @total_employees = (SELECT COUNT(*) FROM employees);
3SELECT @total_employees;  -- 500
4
5-- With a WHERE clause
6SET @dept_count = (SELECT COUNT(*) FROM employees WHERE department = 'Engineering');
7SELECT @dept_count;  -- 120

The subquery must return exactly one row and one column. If it returns multiple rows, MySQL returns an error.

MySQL: SELECT @variable := expression

sql
1-- Assign during a SELECT (MySQL-specific syntax)
2SELECT @row_number := @row_number + 1 AS row_num, name, salary
3FROM employees, (SELECT @row_number := 0) AS init
4ORDER BY salary DESC;
5
6-- Result:
7-- row_num | name    | salary
8-- 1       | Alice   | 150000
9-- 2       | Bob     | 120000
10-- 3       | Charlie | 95000

The := operator assigns within a SELECT. This is deprecated in MySQL 8.0+ in favor of window functions.

Using Variables in Subsequent Queries

sql
1-- Store a value
2SET @target_dept = 'Engineering';
3SET @min_salary = 80000;
4
5-- Use in WHERE clause
6SELECT name, salary
7FROM employees
8WHERE department = @target_dept
9  AND salary >= @min_salary;
10
11-- Use in INSERT
12SET @new_manager = (SELECT id FROM employees WHERE name = 'Alice');
13INSERT INTO departments (name, manager_id)
14VALUES ('New Team', @new_manager);
15
16-- Use in UPDATE
17SET @avg_salary = (SELECT AVG(salary) FROM employees);
18UPDATE employees
19SET bonus = salary * 0.1
20WHERE salary > @avg_salary;

Calculating Running Totals (Pre-Window Functions)

sql
1-- Running total using user variables (MySQL 5.x pattern)
2SET @running_total = 0;
3
4SELECT
5    order_date,
6    amount,
7    @running_total := @running_total + amount AS running_total
8FROM orders
9ORDER BY order_date;
10
11-- Result:
12-- order_date  | amount | running_total
13-- 2025-01-01  | 100    | 100
14-- 2025-01-02  | 250    | 350
15-- 2025-01-03  | 75     | 425

In MySQL 8.0+, use window functions instead:

sql
1-- Modern approach with window functions
2SELECT
3    order_date,
4    amount,
5    SUM(amount) OVER (ORDER BY order_date) AS running_total
6FROM orders;

PostgreSQL: Variables in PL/pgSQL

PostgreSQL does not have session-level user variables like MySQL. Use DO blocks or functions:

sql
1-- PostgreSQL: using DO block
2DO $$
3DECLARE
4    max_salary NUMERIC;
5BEGIN
6    SELECT MAX(salary) INTO max_salary FROM employees;
7    RAISE NOTICE 'Max salary: %', max_salary;
8END $$;
9
10-- PostgreSQL: using CTEs (Common Table Expressions) instead
11WITH stats AS (
12    SELECT MAX(salary) AS max_sal, AVG(salary) AS avg_sal
13    FROM employees
14)
15SELECT e.name, e.salary, s.max_sal, s.avg_sal
16FROM employees e, stats s
17WHERE e.salary > s.avg_sal;

SQL Server: Variables with DECLARE and SET

sql
1-- SQL Server: must DECLARE variables first
2DECLARE @max_salary DECIMAL(10,2);
3DECLARE @emp_count INT;
4
5-- SET with subquery
6SET @max_salary = (SELECT MAX(salary) FROM employees);
7SET @emp_count = (SELECT COUNT(*) FROM employees);
8
9-- Or use SELECT INTO
10SELECT @max_salary = MAX(salary), @emp_count = COUNT(*)
11FROM employees;
12
13-- Use in subsequent queries
14SELECT name, salary
15FROM employees
16WHERE salary = @max_salary;
17
18PRINT 'Total employees: ' + CAST(@emp_count AS VARCHAR);

Practical Example: Pagination

sql
1-- MySQL: paginate results using variables
2SET @page = 3;
3SET @page_size = 10;
4SET @offset_val = (@page - 1) * @page_size;
5
6SELECT name, email
7FROM users
8ORDER BY created_at DESC
9LIMIT @page_size OFFSET @offset_val;
10-- Note: LIMIT/OFFSET don't accept variables in all MySQL versions
11-- Use prepared statements if needed:
12
13SET @sql = CONCAT('SELECT name, email FROM users ORDER BY created_at DESC LIMIT ', @page_size, ' OFFSET ', @offset_val);
14PREPARE stmt FROM @sql;
15EXECUTE stmt;
16DEALLOCATE PREPARE stmt;

Practical Example: Conditional Logic

sql
1-- Store a threshold and use it across multiple queries
2SET @threshold = (
3    SELECT AVG(order_total) * 1.5
4    FROM orders
5    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
6);
7
8-- Find high-value orders
9SELECT order_id, customer_name, order_total
10FROM orders
11WHERE order_total > @threshold;
12
13-- Count high-value customers
14SELECT COUNT(DISTINCT customer_id) AS high_value_customers
15FROM orders
16WHERE order_total > @threshold;

Common Pitfalls

  • Subquery returns multiple rows: SET @var = (SELECT col FROM table) fails if the subquery returns more than one row. Add LIMIT 1 or use an aggregate function (MAX, MIN, COUNT) to ensure a single result.
  • Variable type is determined by the assigned value: MySQL user variables have no declared type. Assigning a string then comparing to a number may produce unexpected results due to implicit type conversion. Be consistent with the types you assign.
  • SELECT @var := expr evaluation order: MySQL does not guarantee the order of evaluation of expressions in a SELECT clause. Using @var := expr in one column and reading @var in another column of the same SELECT may produce unpredictable results. Use window functions in MySQL 8.0+ instead.
  • Variables do not persist across sessions: User variables (@var) exist only for the current session/connection. A new connection starts with no variables set. For persistent storage, use tables.
  • PostgreSQL has no user variables: PostgreSQL does not support @variable syntax. Use DO blocks with DECLARE, CTEs, or temporary tables to achieve similar functionality.

Summary

  • MySQL: SELECT ... INTO @var, SET @var = (SELECT ...), or @var := expr in SELECT
  • User variables are session-scoped, prefixed with @, and do not require declaration in MySQL
  • SQL Server requires DECLARE @var TYPE before use
  • PostgreSQL uses DO blocks with DECLARE or CTEs instead of user variables
  • Avoid SELECT @var := expr in MySQL 8.0+ — use window functions for row-level calculations
  • Subqueries used to set variables must return exactly one row and one column
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.