Online Extra

Chris Date

Fifty Ways to Quote Your Query

A test suite for your SQL optimizer!

Near the end of my June 1998 column, I said "All but the most trivial of queries can be expressed in SQL in a huge number of different ways. Even a query as simple as 'Get names of suppliers who supply part P2' can easily be expressed in at least eight different ways, all of them at least superficially distinct. And those eight different ways are ways that use only features available in the original SQL standard! The number increases dramatically when new features introduced with SQL/92 are taken into account." Now read on:

1. SELECT DISTINCT S.SNAME
FROM S, SP
WHERE S.S# = SP.S# 
AND SP.P# = 'P2'
2. SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# IN
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' )
3. SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# =ANY
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' )
4. SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# MATCH
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' )
5. SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# MATCH UNIQUE
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' )
6. SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# MATCH PARTIAL
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' )
7. SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# MATCH UNIQUE PARTIAL
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' )
8. SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# MATCH FULL
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' )
9. SELECT DISTINCT S.SNAME
FROM S
WHERE S.S# MATCH UNIQUE FULL
( SELECT SP.S#
FROM SP
WHERE SP.P# = 'P2' )
10. SELECT DISTINCT S.SNAME
FROM S
WHERE EXISTS
( SELECT *
FROM SP
WHERE SP.S# = S.S# 
AND SP.P# = 'P2' )
11. SELECT DISTINCT S.SNAME
FROM S
WHERE ( SELECT COUNT(*)
FROM SP
WHERE SP.S# = S.S# 
AND SP.P# = 'P2' ) > 0
12. SELECT DISTINCT S.SNAME
FROM S
WHERE ( SELECT COUNT(*)
FROM SP
WHERE SP.S# = S.S# 
AND SP.P# = 'P2' ) = 1
13. SELECT DISTINCT S.SNAME
FROM S
WHERE 'P2' IN
( SELECT SP.P#
FROM SP
WHERE SP.S# = S.S# )
14. SELECT DISTINCT S.SNAME
FROM S
WHERE 'P2' =ANY
( SELECT SP.P#
FROM SP
WHERE SP.S# = S.S# )
15. SELECT DISTINCT S.SNAME
FROM S
WHERE 'P2' MATCH
( SELECT SP.P#
FROM SP
WHERE SP.S# = S.S# )
16. SELECT DISTINCT S.SNAME
FROM S
WHERE 'P2' MATCH UNIQUE
( SELECT SP.P#
FROM SP
WHERE SP.S# = S.S# )
17. SELECT DISTINCT S.SNAME
FROM S
WHERE 'P2' MATCH PARTIAL
( SELECT SP.P#
FROM SP
WHERE SP.S# = S.S# )
18. SELECT DISTINCT S.SNAME
FROM S
WHERE 'P2' MATCH UNIQUE PARTIAL
( SELECT SP.P#
FROM SP
WHERE SP.S# = S.S# )
19. SELECT DISTINCT S.SNAME
FROM S
WHERE 'P2' MATCH FULL
( SELECT SP.P#
FROM SP
WHERE SP.S# = S.S# )
20. SELECT DISTINCT S.SNAME
FROM S
WHERE 'P2' MATCH UNIQUE FULL
( SELECT SP.P#
FROM SP
WHERE SP.S# = S.S# )
21. SELECT S.SNAME
FROM S, SP
WHERE S.S# = SP.S#
AND SP.P# = 'P2'
GROUP BY S.SNAME
22. SELECT DISTINCT S.SNAME
FROM S, SP
WHERE S.S# = SP.S#
GROUP BY S.SNAME, SP.P#
HAVING SP.P# = 'P2'
23. SELECT DISTINCT S.SNAME
FROM S, SP
WHERE SP.P# = 'P2'
GROUP BY S.S#, S.SNAME, SP.S#
HAVING SP.S# = S.S#
24. SELECT DISTINCT S.SNAME
FROM S, SP
GROUP BY S.S#, S.SNAME, SP.S#, SP.P#
HAVING SP.S# = S.S#
AND SP.P# = 'P2'
25. SELECT DISTINCT S.SNAME
FROM S CROSS JOIN SP
WHERE S.S# = SP.S#
AND SP.P# = 'P2'
26. SELECT DISTINCT S.SNAME
FROM S NATURAL JOIN SP
WHERE SP.P# = 'P2'
27. SELECT DISTINCT S.SNAME
FROM S JOIN SP USING ( S# ) 
WHERE SP.P# = 'P2'
28. SELECT DISTINCT S.SNAME
FROM S JOIN SP ON S.S# = SP.S#
WHERE SP.P# = 'P2'
29. SELECT DISTINCT S.SNAME
FROM S NATURAL LEFT JOIN SP
WHERE SP.P# = 'P2'
30. SELECT DISTINCT S.SNAME
FROM S LEFT JOIN SP USING ( S# )
WHERE SP.P# = 'P2'
31. SELECT DISTINCT S.SNAME
FROM S LEFT JOIN SP ON S.S# = SP.S#
WHERE SP.P# = 'P2'
32. SELECT DISTINCT S.SNAME
FROM S NATURAL RIGHT JOIN SP
WHERE SP.P# = 'P2'
33. SELECT DISTINCT S.SNAME
FROM S RIGHT JOIN SP USING ( S# )
WHERE SP.P# = 'P2'
34. SELECT DISTINCT S.SNAME
FROM S RIGHT JOIN SP ON S.S# = SP.S#
WHERE SP.P# = 'P2'
35. SELECT DISTINCT S.SNAME
FROM S NATURAL FULL JOIN SP
WHERE SP.P# = 'P2'
36. SELECT DISTINCT S.SNAME
FROM S FULL JOIN SP USING ( S# )
WHERE SP.P# = 'P2'
37. SELECT DISTINCT S.SNAME
FROM S FULL JOIN SP ON S.S# = SP.S#
WHERE SP.P# = 'P2'
38. SELECT DISTINCT S.SNAME
FROM S 
NATURAL JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS
39. SELECT DISTINCT S.SNAME
FROM S 
JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS 
USING ( S# )
40. SELECT DISTINCT S.SNAME
FROM S 
JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS 
ON S.S# = SP.S#
41. SELECT DISTINCT S.SNAME
FROM S 
NATURAL LEFT JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS
42. SELECT DISTINCT S.SNAME
FROM S 
LEFT JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS 
USING ( S# )
43. SELECT DISTINCT S.SNAME
FROM S 
LEFT JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS 
ON S.S# = SP.S#
44. SELECT DISTINCT S.SNAME
FROM S 
NATURAL RIGHT JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS
45. SELECT DISTINCT S.SNAME
FROM S 
RIGHT JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS 
USING ( S# )
46. SELECT DISTINCT S.SNAME
FROM S 
RIGHT JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS 
ON S.S# = SP.S#
47. SELECT DISTINCT S.SNAME
FROM S 
NATURAL FULL JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS
48. SELECT DISTINCT S.SNAME
FROM S 
FULL JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS 
USING ( S# )
49. SELECT DISTINCT S.SNAME
FROM S 
FULL JOIN 
( SELECT SP.S# 
FROM SP 
WHERE SP.P# = 'P2' ) AS POINTLESS 
ON S.S# = SP.S#
50. SELECT DISTINCT S.SNAME
FROM ( ( SELECT S.S# FROM S )
INTERSECT 
( SELECT SP.S# FROM SP
WHERE SP.P# = 'P2' ) ) AS POINTLESS
NATURAL JOIN S
51. SELECT DISTINCT S.SNAME
FROM ( ( SELECT * FROM S )
INTERSECT CORRESPONDING 
( SELECT * FROM SP
WHERE SP.P# = 'P2' ) ) AS POINTLESS
NATURAL JOIN S
52. SELECT DISTINCT S.SNAME
FROM ( ( SELECT * FROM S )
INTERSECT CORRESPONDING BY ( S# )
( SELECT * FROM SP
WHERE SP.P# = 'P2' ) ) AS POINTLESS
NATURAL JOIN S

Note: Hugh Darwen and I fully describe all the SQL constructs used in the foregoing formulations in the book A Guide to the SQL Standard (Addison Wesley, 1997).

DISCUSSION

OK, it wasn't 50; it was 52. But, of course, the stopping place was arbitrary! Consider the following:

• We can obtain numerous further (albeit rather frivolous) variants on the formulations already shown by making use of the fact that (for example) a < b is logically equivalent to b > a or the fact that a NATURAL JOIN b is logically equivalent to b NATURAL JOIN a, or the fact that a LEFT JOIN b is logically equivalent to b RIGHT JOIN a. (Actually, these last two equivalencies aren't quite valid in SQL, owing to the fact that in SQL columns have a left-to-right ordering, but this flaw doesn't invalidate my basic point.)

• I didn't bother to show various possible "tricky" formulations that made use of comparison operators such as LIKE and BETWEEN, nor ones that made use of the 'TABLE table' construct.

• The three INTERSECT formulations shown (numbers 50 through 52) each make use of a join. In each case, the NATURAL JOIN shown could be replaced by a JOIN USING, JOIN ON, LEFT NATURAL JOIN, LEFT JOIN USING, or LEFT JOIN ON to produce another valid formulation of the original query.

• The many SELECT clauses that appear in the various formulations can always include the DISTINCT option. (Exercise for the reader: Is the converse ever true? That is, can the DISTINCT option be dropped from any of the SELECT clauses that currently contain it?)

• Let exp be an SQL formulation of the query "get suppliers who don't supply part P2." For example:

SELECT *
FROM S
WHERE NOT EXISTS
( SELECT *
FROM SP
WHERE SP.S# = S.S#
AND SP.P# = 'P2' 

(It should be no surprise to you by now that there are any number of logically equivalent formulations, of course.) Then the expression:

SELECT DISTINCT S.SNAME 
FROM ( S EXCEPT ( exp ) )

is yet another valid SQL formulation of our original query. Furthermore, this EXCEPT formulation itself has several variants (analogous to the variants already discussed in connection with the INTERSECT formulations).

• Finally, let exp be any valid SQL formulation of the original query. Then the expression:

SELECT DISTINCT SNAME
FROM ( exp ) AS POINTLESS

is another valid formulation! In other words, the number of valid formulations is quite literally infinite (in theory at least, though of course any given SQL implementation will have limits on such things as the maximum length of a SQL expression).

One final remark. It's true, of course, that many of the formulations I've shown-perhaps most of them-would never be directly produced by an end user as a 'natural' formulation of what is, after all, an almost trivial query. But this observation misses the point! First, if there are so many ways to formulate such a simple query, how many ways are there going to be for a complicated one? Second, even in the simple case, the user might be querying a view rather than a base table, and the 'query merge' process (that is, the process of combining the user's query and the view definition) might easily yield a fairly complex expression.

Check out your optimizer!
 

C. J. Date is an independent author, lecturer, researcher, and consultant, specializing in relational database systems. His most recent books, all published by Addison-Wesley, are An Introduction to Database Systems (6th edition, 1995); Relational Database Writings 1991-1994 (1995); and (with Hugh Darwen) A Guide to the SQL Standard (4th edition, 1997). Correspondence may be sent to him in care of Database Programming & Design , 411 Borel Ave., Ste. 100, San Mateo, CA 94402.