EXCEPT it exits in 2 other tables

Question

I need to get all values from table product , EXCEPT they exits in 2 other table products. Is this query acceptable using 2 EXCEPTS ? Should this be done differently .

CREATE table missing_values
AS
select post
from product
EXCEPT
select post
from product_table_a
EXCEPT
select post
from product_table_b
;

Your question is unclear. If a value exists in `product` and only ONE of the other two tables, should it be excluded by your query ? Or should it only be excluded if it exists in BOTH of the other two tables? Also ***have you actually tried it yourself***? — MatBailie, Sep 07 '15 at 16:22
@JuanCarlosOropeza - Please don't make edits that add no material benefit. — MatBailie, Sep 07 '15 at 16:26
@ MatBailie - If there is a value in product table not found in product_table_a or product_table_b it should be added to the missing_values table being created , this is it. — Jorge Vidinha, Sep 07 '15 at 16:30
@Craig Ringer . Right first posted at dba , there was no audience . Than posted here . Thanks for noticing — Jorge Vidinha, Sep 07 '15 at 23:26
@JorgeVidinha No probs. The cross links just help people find it and reduce time wasted by asking duplicate followup questions. It's nice if you include cross links in the question if you're going to post multiple places. BTW, remember timezones and people's work. 8 hours isn't "no audience", it's just "not answered instantly" — Craig Ringer, Sep 07 '15 at 23:28
@CraigRinger good to know , thanks. Btw any advice on the question ? — Jorge Vidinha, Sep 07 '15 at 23:31
Related answer with basic techniques: [Select rows which are not present in other table](http://stackoverflow.com/a/19364694/939860) — Erwin Brandstetter, Sep 08 '15 at 02:55

score 2 · Answer 1 · answered Sep 07 '15 at 16:30

2

As MatBailie say, you should try your query and see if that give you the result you want.

But in case that doesnt work you can solve it with a double not exists

This mean p1 isn't found neither in p2 or p3.

SELECT post
FROM product p1
WHERE not exists (SELECT p2.post                       
                  FROM product_table_a p2
                  WHERE p1.post = p2.post)
AND   not exists (SELECT p3.post                       
                  FROM product_table_b p3
                  WHERE p1.post = p3.post)

Also this is a probably a more eficient way

SELECT post
FROM product p1
left join product_table_a p2
   on p1.post = p2.post
left join product_table_b p3
   on p1.post = p3.post
WHERE p2.post is null
and   p3.post is null

answered Sep 07 '15 at 16:30

Juan Carlos Oropeza

47,252
12
78
118

3

For what it is worth [using `LEFT JOIN/IS NULL` is no more or less efficient in Postgresql](http://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/) than using `NOT EXISTS` – GarethD Sep 07 '15 at 16:39
@GarethD I didn't knew that. – Juan Carlos Oropeza Sep 07 '15 at 16:42
@JuanCarlosOropeza Yep. The query planner typically produces the same plan for either form. – Craig Ringer Sep 07 '15 at 23:21

score 1 · Accepted Answer · answered Sep 07 '15 at 17:18

1

A - B - C is the same as A - (B + c):

SELECT post
FROM product
EXCEPT
(
  SELECT post
  FROM product_table_a
  UNION ALL
  SELECT post
  FROM product_table_b
) AS sum;

answered Sep 07 '15 at 17:18

Lukasz Szozda

162,964
23
234
275

Just wondering. Any reason to use `union all` over just `union` ? – Juan Carlos Oropeza Sep 07 '15 at 18:10
@JuanCarlosOropeza `UNION ALL` is faster and doesn't require sorting – Lukasz Szozda Sep 07 '15 at 18:29

EXCEPT it exits in 2 other tables

2 Answers2