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;