Combine queries

From SQLZOO
Jump to navigation Jump to search

Here you are shown how to combine multiple queries.

Both related and unrelated queries can be merged,

if the queries are often used together then combining them together can greatly improve performance.

Table 1 and 2 show the two separate tables and Table 3 shows the result you would obtain from combining queries.

Table 1
contentPage name
helloindex.html
Hiaindex.html
page2p2.html
Indexcontents.html
Table 2
Message
The site will be down on Tuesday
Table 3
pagenamecontentNULLpage
index.htmlhellopage
index.htmlHiapage
The site will be down on Tuesdaymotd
schema:scott
DROP TABLE page;
DROP TABLE motd;
 CREATE TABLE page (
   content TEXT,
   pagename TEXT );
INSERT INTO page VALUES ('hello','index.html');
INSERT INTO page VALUES ('Hia','index.html');
INSERT INTO page VALUES ('page2','p2.html');
INSERT INTO page VALUES ('Index','contents.html');
CREATE TABLE motd (
  message VARCHAR(100));
INSERT INTO motd VALUES ('The site will be down on Tuesday');

In this example a typical approach to these tables could be:

SELECT pagename, content
  FROM page
 WHERE pagename = 'index.html'

or:

SELECT message FROM motd

These two queries can be combined using UNION and NULLs where necessary and therefore a single query can be run allowing a quicker response from the database.

SELECT pagename, content, NULL, 'page'
  FROM page
 WHERE pagename = 'index.html'
 UNION 
 SELECT NULL, NULL, message, 'motd'
   FROM motd