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
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 ...)
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
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
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)
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:
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:
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
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);
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
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
