I am reading FUNDAMENTALS OF DATABASE SYSTEMS where it says "In general, a
query written with nested SELECT...FROM...WHERE... blocks and using = or IN
comparison operators can always be expressed as a single block query."

I believe this is true.

Now I wonder whether similar structure with 'NOT IN' comparison operator
can be expressed as a single block query.

Can any body give me an example?

On Jul 28, 1:41 am, "John Ruan" <rds1... (AT) sh163 (DOT) net> wrote:
1) What means a block? Is it mean one (SELECT...FROM...WHERE...)?
2) Another point is "using = or IN comparison operators".
Right of = or IN is limited in an expression or a list of expressions?
Or more generally, it include a sub-select?
For example:
col_a = ANY (SELECT...FROM...WHERE...)
or
col_a IN (SELECT...FROM...WHERE...)

I will asuume both of them.

Followings were not investigated fully, just my understandings and
ideas.

SELECT col_a, col_b, ...
FROM (SELECT expression_a1, expression_a2, ...
FROM <table-reference-a>
WHERE condition_a) S(col_a, col_b, ...)
WHERE col_a IN (SELECT expression_b
FROM <table-reference-b>
WHERE condition_b);
(Where <table-reference> containes table, view, join of them and/or
union, except or intersect of sub-query.)

can be written:
SELECT DISTINCT expression_a1 AS col_a, expression_a2 AS col_b, ...
FROM <table-reference-a>
INNER JOIN
<table-reference-b>
ON condition_a
AND condition_b
AND expression_a1 = expression_b;

For NOT IN case:
SELECT col_a, col_b, ...
FROM (SELECT expression_a1, expression_a2, ...
FROM <table-reference-a>
WHERE condition_a) S(col_a, col_b, ...)
WHERE col_a NOT IN (SELECT expression_b
FROM <table-reference-b>
WHERE condition_b);

can be written:
SELECT expression_a1 AS col_a, expression_a2 AS col_b, ...
FROM <table-reference-a>
LEFT OUTER JOIN
<table-reference-b>
ON condition_a
AND condition_b
AND expression_a1 = expression_b
WHERE expression_b IS NULL;

I made mistake.

For NOT IN case:
SELECT col_a, col_b, ...
FROM (SELECT expression_a1, expression_a2, ...
FROM <table-reference-a>
WHERE condition_a) S(col_a, col_b, ...)
WHERE col_a NOT IN (SELECT expression_b
FROM <table-reference-b>
WHERE condition_b);

Rewrited Query should be:
SELECT expression_a1 AS col_a, expression_a2 AS col_b, ...
FROM <table-reference-a>
LEFT OUTER JOIN
<table-reference-b>
ON condition_b
AND expression_a1 = expression_b
WHERE condition_a
AND expression_b IS NULL;

Thanks!

Your solution works in Mysql.


"Tonkuma" <tonkuma (AT) fiberbit (DOT) net> wrote