Skip to main content
explain
Source Link
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

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.

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.

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.

Source Link
Erwin Brandstetter
  • 182.1k
  • 28
  • 457
  • 620

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.