Difference between revisions of "Combine queries"

From SQLZOO
Jump to: navigation, search
(Created page with "<p>Here you are shown how to combine multiple queries.</p> <p>Both related and unrelated queries can be merged,</p> <p>if the queries are often used together then combining th...")
 
 
Line 1: Line 1:
 
<p>Here you are shown how to combine multiple queries.</p>
 
<p>Here you are shown how to combine multiple queries.</p>
 
<p>Both related and unrelated queries can be merged,</p>
 
<p>Both related and unrelated queries can be merged,</p>
<p>if the queries are often used together then combining them together can greatly improve performence.</p>
+
<p>if the queries are often used together then combining them together can greatly improve performance.</p>
 
<p>Table 1 and 2 show the two separate tables and Table 3 shows the result you would obtain from combining queries.</p>
 
<p>Table 1 and 2 show the two separate tables and Table 3 shows the result you would obtain from combining queries.</p>
 
<div class="ref_section">
 
<div class="ref_section">

Latest revision as of 10:33, 8 August 2012

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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense