Join query with not equal

Question

I want to get all unpaid male customers those who are not in any plan

SELECT cr.id, cr.type FROM mydb.customer cr 
JOIN mydb.plan1 p1 on cr.id != p1.id 
JOIN mydb.plan2 p2 on cr.id != p2.id 
JOIN mydb.plan3 p3 on cr.id != p3.id 
WHERE cr.type = 'male'

is this query correct?

How does "unpaid" factor into this? Also: Any such question would best declare the Postgres version in use and a table definition. — Erwin Brandstetter, May 09 '16 at 06:34

Tim Biegeleisen · Accepted Answer · 2016-05-09T06:26:52.870

4

You could use a series of three left joins along with IS NULL:

SELECT cr.id, cr.type
FROM mydb.customer cr 
LEFT JOIN mydb.plan1 p1
    ON cr.id = p1.id 
LEFT JOIN mydb.plan2 p2
    ON cr.id = p2.id
LEFT JOIN mydb.plan3 p3
    ON cr.id = p3.id
WHERE p1.id IS NULL AND p2.id IS NULL AND p3.id iS NULL AND
    cr.type = 'male'

edited May 09 '16 at 06:26

answered May 09 '16 at 06:08

Tim Biegeleisen

502,043
27
286
360

1

I think the query seems incorrect. the query uses left join (which usually defaults to inner join) and thus your query will almost always yield zero results (except P3 where you have used a != clause) – vvs May 09 '16 at 06:15
@vvs I fixed the typo. This answer does not deserve a downvote. – Tim Biegeleisen May 09 '16 at 06:16
I think now the query will always yield zero results. Cause left join will yield only results where a customer has all 3 plans. To fix consider using outer join. Have removed down vote. – vvs May 09 '16 at 06:20
@vvs You are wrong. `LEFT JOIN` always show _all_ customers from the `customer` table regardless of whether each customer matches a plan. – Tim Biegeleisen May 09 '16 at 06:21
@Tom yeah .. I think I have confused left join and inner join... sorry. – vvs May 09 '16 at 06:25
@ws: Yes you seem to have confused those two. This answer works - except for the typo with `==`. – Erwin Brandstetter May 09 '16 at 06:26

score 1 · Answer 2 · edited May 23 '17 at 12:16

Since all you seem to need is the id, EXCEPT should be a good choice here:

SELECT id FROM mydb.customer WHERE type = 'male'
EXCEPT ALL SELECT id FROM mydb.plan1
EXCEPT ALL SELECT id FROM mydb.plan2
EXCEPT ALL SELECT id FROM mydb.plan3;

To be precise: EXCEPT ALL:

Using EXCEPT clause in PostgreSQL

Basic techniques:

Select rows which are not present in other table

Multiple joins may not perform as fast if each table can have multiple related rows due to multiplication of rows in the intermediary derived table. Just test performance with EXPLAIN ANALYZE.

Join query with not equal

2 Answers2