Postgresql – LEFT JOIN same table – Postgres 9.2

join;postgresqlpostgresql-9.2select

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?

Best Answer

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.

Related Solutions

MySQL select with left join query

I updated class_teachers table to class_teachers_section to include section. The idea is a class can include different sections with different teachers. For example a grade 1 PE class can include all sections; the teacher for boys will be different from that for girls.

select class.name as classname, 
   class.hours as hours, 
   teachers.first_name as teacherFirstName, 
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls 
 left join class on class.id = cls.class_ref  
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'

Mysql – Cross Join and Left Join within a query

Your query

SELECT b.*
FROM
a,b
LEFT JOIN c ON hour(c.datetime) = a.hour;

means (parentheses added)

SELECT b.*
FROM
a,
(b LEFT JOIN c ON hour(c.datetime) = a.hour);

and that means a is not visible in the join. You may need to shuffle it around a bit

SELECT b.*
FROM
(a LEFT JOIN c ON hour(c.datetime) = a.hour), b;

http://sqlfiddle.com/#!9/4114a/2

That said, such cross-join seems weird, so make sure you actually need it that way.

Modified after the comment http://sqlfiddle.com/#!9/4114a/3

SELECT b.*
FROM
(a,b)
LEFT JOIN c ON hour(c.datetime) = a.hour;

Best Answer

Related Solutions

MySQL select with left join query

Mysql – Cross Join and Left Join within a query

Related Question