LEFT JOIN same table - Postgres 9.2

Question

I've got this table:

CREATE TABLE clients (
 id BIGSERIAL NOT NULL,
 company CHARACTER VARYING(255) DEFAULT '' NOT NULL,
 email CHARACTER VARYING(50) DEFAULT '' NOT NULL,
 city CHARACTER VARYING(80), 
 postcode CHARACTER VARYING(20),
);

I got a list of cities and postcode. I need to find if any row matches with the data I have:

City,Zipcode
Lansdowne,2163
Villawood,2163
Smithfield,2164

I'm using this query; how can I achieve the expected result?

SELECT 
 t1.id,
 t1.city,
 t1.company,
 t1.postcode
FROM 
 clients t1 
LEFT JOIN 
 clients t2 ON t2.city = t1.city
LEFT JOIN 
 clients t3 ON t3.postcode = t1.postcode
WHERE t2.city IN ('Lansdowne','Villawood','Smithfield',) 
AND t3.postcode IN ('2164','2163','2163',)

Would the query be right?

• a_vlad: what wrong if exclude JOIN and leave only WHERE?

Answer 1

Would the query be right?

No. It would find any combination of given cities and postcodes (like ('Smithfield', '2163'). This Cartesian product would become increasingly expensive and nonsensical for more combinations in one query.

And the query itself is needlessly expensive and confusing on top of that. LEFT JOIN is misplaced and joins would not be needed.

The shortest, cleanest and fastest way to match on multiple rows (combination of multiple column values) is to join to a VALUES expression (which is an ad-hoc table expression):

SELECT id, city, company, postcode
FROM clients
JOIN (
 VALUES
 ('Lansdowne' , '2163')
 , ('Villawood' , '2163')
 , ('Smithfield', '2164')
 ) v(city, postcode) USING (city, postcode);

For a big table, to make this fast, you should have an index on (city, postcode) or vice versa or at least an index with these two as leading columns.

Answer 2

this query return data, but what the reason of this query if same result You can achieve without un-necessary JOINs?

SELECT 
 t1.id,
 t1.city,
 t1.company,
 t1.postcode
FROM 
 clients t1 
WHERE t1.city IN ('Lansdowne','Villawood','Smithfield',) 
AND t1.postcode IN ('2164','2163','2163',)

this query same as original form not check City+Postcode (allow any combination), more strong form:

SELECT 
 t1.id,
 t1.city,
 t1.company,
 t1.postcode
FROM 
 clients t1 
WHERE (t1.city = 'Lansdowne' AND t1.postcode = '2164) OR (t1.city = 'Villawood' AND t1.postcode = '2163') OR (t1.city = 'Smithfield' AND t1.postcode = '2163')