current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have Table employee, Table sport that have n to m relations, that's means, i also have one more table, let's say table emp_sport

table employee

emp_id emp_name
------------------
e1 name1
e2 name2
e3 name3
e4 name4

table sport
sport_id sport_name
---------------------
sp1 spName1
sp2 spName2
sp3 spName3

table emp_sport
emp_id sport_id
-------------------
e1   sp1
e1   sp2
e1   sp3
e2   sp2

i want to select data from employee like this

select distinct(e.emp_id), e.emp_name 
from employee e 
where e.emp_id not in (select sp.emp_id from sport_emp sp where sp.sport_id = 'sp1')

the result, i have 

e2 name2
e3 name3
e4 name4

the problem is, when i have very huge data in table employee and also emp_sport, query was very slow.

share|improve this question
1  
It's usually better to use NOT EXISTS (SELECT 1 WHERE ...) than NOT IN (SELECT ...). Also, your NOT IN (SELECT ...) will probably produce unexpected results unless sp.emp_id is NOT NULL. For query performance questions you need to include more information; see stackoverflow.com/tags/postgresql-performance/info for what you need to add by editing your question (not comments). –  Craig Ringer Feb 7 '14 at 7:10

1 Answer 1

up vote 0 down vote

Always use joins if possible. Subquery will always be a reason of lack of performance.

Try This

select e."emp_id",e."emp_name"
from "employee" e
inner join "emp_sport" es on es."emp_id"=e."emp_id"
where es."sport_id"='sp1'
group by e."emp_id",e."emp_name"
share|improve this answer
    
Actually, a subquery is frequently optimized into a join anyway. You can usually write it however you find most readable, though there are certainly exceptions. A NOT EXISTS query, for example, typically optimizes into a left anti-join. –  Craig Ringer Feb 7 '14 at 7:09
    
And a join is not necessary a replacement for a (co-related) sub-query. It might return different results –  a_horse_with_no_name Feb 7 '14 at 7:17
    
thx u fro your answer, actually i want the oposite result from your code above, SELECT e."emp_id",e."emp_name" FROM "employee" e LEFT OUTER JOIN "emp_sport" es on es."emp_id"=e."emp_id" and es."sport_id"='sp1' WHERE e."emp_id" IS null –  user3282312 Feb 7 '14 at 7:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.