Re: replacing a subquery with an outer join?
От | Gavin Flower |
---|---|
Тема | Re: replacing a subquery with an outer join? |
Дата | |
Msg-id | 4E28D644.3030804@archidevsys.co.nz обсуждение исходный текст |
Ответ на | replacing a subquery with an outer join? (Chris Curvey <chris@chriscurvey.com>) |
Список | pgsql-general |
On 22/07/11 13:20, Chris Curvey wrote:
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;
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 responded3. Also, you are using a correlated sub-query instead of a LEFT OUTER JOINThis 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;
В списке pgsql-general по дате отправления: