SELECT...INTO can be used to store the single row column result value from a query to be used in another query depending on that value.
"accounts" table
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| account_id | int(11) | NO | PRI | NULL | auto_increment |
| account_name | varchar(50) | NO | | NULL | |
| balance | float | NO | | NULL | |
| state | varchar(50) | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
Sample data
+------------+--------------+---------+-------+
| account_id | account_name | balance | state |
+------------+--------------+---------+-------+
| 1 | Rico | 500 | IL |
| 2 | Mark | 100 | NY |
| 3 | Rita | 50 | NY |
+------------+--------------+---------+-------+
Lets say we want to get average balance of all accounts in the state of NY from the above table and then use that value in another query.
mysql> SELECT AVG(balance) INTO @avg_bal
-> FROM accounts
-> WHERE state = 'NY'
-> GROUP BY state;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CONCAT('Average balance in NY is ', @avg_bal) AS result;
+-----------------------------+
| result |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)
The above use case arises when we have 2 or more queries depending on each others' output inside a stored procedure.
Below is how the stored procedure would look:
DELIMITER $$
DROP PROCEDURE IF EXISTS get_ny_avg_bal$$
CREATE PROCEDURE get_ny_avg_bal()
BEGIN
DECLARE avg_bal FLOAT;
SELECT AVG(balance) INTO avg_bal
FROM accounts
WHERE state = 'NY'
GROUP BY state;
SELECT CONCAT('Average balance in NY is ', avg_bal) AS result;
END$$
DELIMITER ;
mysql> CALL get_ny_avg_bal();
+-----------------------------+
| result |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
