Can MySQL return a multiple result set with a single query?

Question

I have the following (returning two separate result sets) that succeeds from proc, but cannot do the same when doing this as a base query.

SELECT * FROM persons; SELECT * FROM addresses; 

Possible? What is the syntax?

EDIT:

I am using the Ruby DBI library:

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

Answer 1

Are you talking about mysql cli? works great 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 are talking about a specific language, it depends on your mysql library. for example, the mysql PHP library does not support this. however, the mysqli library does if you use multi_query ().

Answer 2

COMBINE people and addresses, and you can get a table with a great result, assuming that the addresses correlate with persons with a specific identifier. If the two queries are not related, why do you want them together?

Answer 3

If the data is correlated, use JOIN to attach the address elements to the person elements. If your tables contain the same columns, you probably want the UNION SELECT to look like this:

SELECT * FROM people UNION SELECT * FROM addresses;