Is MySQL able to return mutiple result set with one query?

Question

I have the following (returning two separate result sets) being successfully executed from a proc but cannot do the same when executing this as a basic query.

SELECT * FROM persons;
SELECT * FROM addresses;

Possible? What's the syntax?

EDIT:

I am using Ruby's DBI library:

dbh.query("SELECT * FROM persons; SELECT * FROM addresses;")

Answer 1

JOIN persons and addresses, and you can get a big result table, assuming addresses correlates to persons with some identifier. If the two queries aren't related, why would you want them together?

Answer 2

are you talking about from the mysql cli? works fine for me:

mysql> select count(*) from a; select count(*) from a;
+----------+
| count(*) |
+----------+
|     2050 |
+----------+
1 row in set (0.06 sec)

+----------+
| count(*) |
+----------+
|     2050 |
+----------+
1 row in set (0.00 sec)

if you're talking about a specific language, then it depends on your mysql library. for example, the PHP mysql library does not support this. however, the mysqli library does if you use multi_query().

Answer 3

If the data correlates use a JOIN to join address items onto person items. If your tables both contain similar columns you probably want a UNION SELECT like so:

SELECT * FROM people UNION SELECT * FROM addresses;