Re: replacing a subquery with an outer join?

On 22/07/11 13:20, Chris Curvey wrote:
> in an earlier thread, the following query was submitted:
>
> SELECT COUNT(*) FROM q
> WHERE NOT EXISTS (SELECT 1
> FROM t AS t
> WHERE t.mid = q.mid);
>
>
> and as part of his answer, David Johnson responded
>
>
> 3. Also, you are using a correlated sub-query instead of a LEFT
> OUTER JOIN
>
>
> This interests me. How would you rewrite the correlated subquery
> above using the outer join?
>
> --
> e-Mail is the equivalent of a postcard written in pencil. This
> message may not have been sent by me, or intended for you. It may
> have been read or even modified while in transit. e-Mail disclaimers
> have the same force in law as a note passed in study hall. If your
> corporate attorney says that you need an disclaimer in your signature,
> you need a new corporate attorney.

DROP TABLE IF EXISTS q;
DROP TABLE IF EXISTS t;

CREATE TABLE q
(
mid int PRIMARY KEY,
qnote text
);

CREATE TABLE t
(
mid int PRIMARY KEY,
tnote text
);

INSERT INTO q (mid, qnote) VALUES
(1, 'first'),
(2, 'second'),
(4, 'fourth');

INSERT INTO t (mid, tnote) VALUES
(1, 'first'),
(3, 'third');

SELECT
COUNT(*)
FROM
q LEFT OUTER JOIN t ON (q.mid = t.mid)
WHERE
t.mid IS NULL;