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.

Table 1

content	Page name
hello	index.html
Hia	index.html
page2	p2.html
Index	contents.html

Table 2

Message
The site will be down on Tuesday

Table 3

pagename	content	NULL	page
index.html	hello		page
index.html	Hia		page
		The site will be down on Tuesday	motd

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

SQL HACKS

Hack 10 Converting subqueries into joins

Hack 11 Converting aggregate subqueries into joins

Hack 14 Generate Combinations

Hack 16 Search for a String across columns

Hack 24 Multiply Across a Result Set

Hack 25 Keep a running total

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 70 Combine your queries

Hack 72 Extract a subset of the results

Hack 78 Break it down by Range

Hack 88 Test two values from a subquery

Hack 98 Find and stop long running queries

Hack 100 Run SQL from a Web Page