Re: [GENERAL] replacing a subquery with an outer join?

[Gavin Flower]

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;