Slide 15.13: A weakness of SQL: Too many equivalent forms

Slide 15.12: The EXISTS predicate
Slide 6.1: Normalization: preliminaries
Home Print version

A Weakness of SQL

There are usually many equivalent SQL forms for one question:

Pro: The multiplicity is for user-friendly.

Con: An important disadvantage of all these different forms is that it seems difficult on first encounter to mentally make an exhaustive search of the alternatives available in posing a query.

Question XVI (Equivalent Forms)
Retrieve the city names containing customers who order product p01.

SELECT DISTINCT city FROM customer WHERE cid IN (
  SELECT cid FROM order3 o WHERE pid = 'p01' );

SELECT DISTINCT city FROM customer WHERE cid = ANY (
  SELECT cid FROM order3 o WHERE pid = 'p01' );

SELECT DISTINCT city FROM customer c WHERE EXISTS (
  SELECT * FROM order3 WHERE cid = c.cid AND pid = 'p01' );

SELECT DISTINCT city FROM customer c, order3 o 
  WHERE o.cid = c.cid AND o.pid = 'p01';

SELECT DISTINCT city FROM customer c WHERE 'p01' IN (
  SELECT pid FROM order3 o WHERE cid = c.cid );

The predicate IN is identical to =ANY.

order3
orderno	month	cid	aid	pid	qty	dollar
1011	jan	c001	a01	p01	1000	450.00
1012	jan	c001	a01	p01	1000	450.00
1019	feb	c001	a02	p02	400	180.00
1017	feb	c001	a06	p03	600	540.00
1018	feb	c001	a03	p04	600	540.00
1023	mar	c001	a04	p05	500	450.00
1022	mar	c001	a05	p06	400	720.00
1025	apr	c001	a05	p07	800	720.00
1013	jan	c002	a03	p03	1000	880.00
1026	may	c002	a05	p03	800	704.00
1015	jan	c003	a03	p05	1200	1104.00
1014	jan	c003	a03	p05	1200	1104.00
1021	feb	c004	a06	p01	1000	460.00
1016	jan	c006	a01	p01	1000	500.00
1020	feb	c006	a03	p07	600	600.00
1024	mar	c006	a06	p01	800	400.00

customer
cid	cname	city	discnt
c001	TipTop	Duluth	10.00
c002	Basics	Dallas	12.00
c003	Allied	Dallas	8.00
c004	ACME	Duluth	8.00
c006	ACME	Kyoto	0.00

⇒

city
Duluth
Kyoto

Demonstration
Below is an SQL test area from W3Schools, which uses the well-known Northwind sample database. The tables here are for read only because of the problem of embedding the scripts. For a fully working example, check this by using Chrome.

Result:

The Database includes:

Tablename	Record
Customers	91
Categories	8
Employees	10
OrderDetails	518
Orders	196
Products	77
Shippers	3
Suppliers	29

W3C WebSQL

WebSQL Browser Support