Combine queries
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.
content | Page name |
---|---|
hello | index.html |
Hia | index.html |
page2 | p2.html |
Index | contents.html |
Message |
---|
The site will be down on Tuesday |
pagename | content | NULL | page |
---|---|---|---|
index.html | hello | page | |
index.html | Hia | page | |
The site will be down on Tuesday | motd |
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
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
Hack 25.5 Splitting and combining columns
Hack 26 Include the rows your JOIN forgot
Hack 30 Calculate the maximum/minimum of two fields
Hack 33 Get values and subtotals in one shot
Hack 50 Combine tables containing different data
Hack 51/52 Display rows as columns
Hack 55 Import Someone Else's Data
Hack 62 Issue Queries Without Using a Table
Hack 63 Generate rows without tables
Hack 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery