Sometimes it’s nice to combine multiple database queries into one to save on round-trips, or for any other reason fetch results from separate tables in a single query without actually joining each of the tables involved. This can be done by effectively selecting the results from the rows of one query into the columns of another.

First, an example table setup; let’s say we’re collecting videos and screenshots for various websites:

CREATE TABLE IF NOT EXISTS `screenshots` (
`id` int(11) NOT NULL,
`site_id` int(11) NOT NULL,
`title` varchar(255) NOT NULL
);

INSERT INTO `videos` (`id`, `site_id`, `duration`) VALUES(1, 1, 532);
INSERT INTO `videos` (`id`, `site_id`, `duration`) VALUES(1, 2, 331);

And now, the magic query, which selects the number of screenshots and the number of videos for a single site in a single query:

select
max(if(type='screenshots',num,'')) as num_screenshots,
max(if(type='videos',num,'')) as num_videos
from (
(select count(s.id) as num, 'screenshots' as type from screenshots s where s.site_id = 1)
UNION ALL
(select count(v.id) as num, 'videos' as type from videos v where v.site_id = 1)
) as foo

If you want this information for, say, all sites, we could say:

select
site_id,
max(if(type='screenshots',num,'')) as num_screenshots,
max(if(type='videos',num,'')) as num_videos
from (
(select s.site_id, count(s.id) as num, 'screenshots' as type from screenshots s where s.site_id in (1,2,3) group by site_id)
UNION ALL
(select v.site_id, count(v.id) as num, 'videos' as type from videos v where v.site_id in (1,2,3) group by site_id)
) as foo group by site_id

In Oracle something similar can be achieved using the decode or case operators.