Slide 15.8: Subqueries

Slide 15.7: Using the same table more than once
Slide 15.9: The IN predicate
Home Print version

Subqueries

A SELECT statement form appearing within another SELECT statement is known as a subquery. A subquery can appear in the search-condition of a WHERE clause for another SELECT statement.

The IN Predicate
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.

SELECT [ ALL | DISTINCT ] expression-1 {, expression-2 }
  FROM table-name-1 [ corr-name-1 ] {, table-name-2 [ corr-name-2 ] }
  [ WHERE condition-1 ]
  [ GROUP BY column-name-1, {, column-name-2 } ]
  [ HAVING condition-2 ];

Condition: expr [NOT] IN (subquery) | expr [NOT] IN ( val {, val ...} )

Question VIII (The IN Predicate)

Get cid values of customers who place orders with agents in Duluth or Dallas.

The query could be done in two steps:

Question VIII.a: Find all agents based in Duluth or Dallas.

agent
aid	aname	city	percent
a01	Smith	New York	6
a02	Jones	Newark	6
a03	Brown	Tokyo	7
a04	Gray	New York	6
a05	Otasi	Duluth	5
a06	Smith	Dallas	5

⇒

aid
a05
a06

(a)
⇓

SELECT aid FROM agent
  WHERE city = 'Duluth' OR city = 'Dallas';

There are many other SQL operators like

% (modulo),
EXISTS,
BETWEEN, and
LIKE.

For a complete list, check SQL operators.

Question VIII.b: The above result can be used to represent a set of values.

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

⇐

aid
a05
a06

(a)

⇒

cid
c001
c002
c004
c006

(b)

SELECT DISTINCT cid FROM order3
  WHERE aid IN ( SELECT aid FROM agent
    WHERE city = 'Duluth' OR city = 'Dallas' );

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