get distinct records without NOT IN query

Question

I have three tables (admin,domain,user) as shown below

'admin'(admin_id, email, domain_id, pass)
'domain'(domain_id, name)
'user' (user_id, email, domain_id, pass)

Admin table:

+--------------------------------+         
| admin_id | email | domain_id  |          
+--------------------------------+         
| 1        | aaa   | 2034       |        
| 2        | bbb   | 3034       |
| 3        | ccc   | 2034       | 
+--------------------------------+

User table

+--------------------------------+         
| user_id | email | domain_id  |          
+--------------------------------+         
| 11        | aaa   | 2034       |        
| 12        | bbb   | 3034       |
| 13        | ccc   | 2034       | 
| 15        | ddd   | 2034       | 
| 16        | eee   | 3034       |
+--------------------------------+

Domain table:

+-----------------------+         
| domain_id | name    |          
+-----------------------+         
| 2034      | aaa.com |        
| 3034      | bbb.com |
+-----------------------+

Output:

+------------+
|ddd@aaa.com |
|eee@bbb.com |
+------------+

So i want records from user table which are not present in admin table.

Is there any way to this without NOT IN query?

Vikdor · Accepted Answer · 2012-10-27T11:05:58.210

2

Is there any way to this without NOT IN query?

Yes, using LEFT JOIN of user with admin.

If id attribute in user and admin tables is used to decide if the two records belong to the same user, then your LEFT JOIN would be on this attribute, as follows:

SELECT CONCAT(u.email, CONCAT('@', d.name))
FROM 
    user u
    JOIN domain d
    ON u.domain_id = d.domain_id
    LEFT JOIN admin a
    ON u.id = a.id
WHERE 
    a.id IS NULL

If id attribute alone is not enough and you need domain_id as well to decide that two records belong to the same user, then

SELECT CONCAT(u.email, CONCAT('@', d.name))
FROM 
    user u
    JOIN domain d
    ON u.domain_id = d.domain_id
    LEFT JOIN admin a
    ON u.id = a.id AND u.domain_id = a.domain_id
WHERE 
    a.id IS NULL

Edit:

As per your update to the question, since email and domain are shared by both admin and user tables, you should LEFT JOIN these tables on those two columns:

SELECT CONCAT(u.email, CONCAT('@', d.name))
FROM 
    user u
    JOIN domain d
    ON u.domain_id = d.domain_id
    LEFT JOIN admin a
    ON u.email = a.email AND u.domain_id = a.domain_id
WHERE 
    a.email IS NULL

edited Oct 27 '12 at 11:05

answered Oct 27 '12 at 09:24

Vikdor

23,934
10
61
84

please note that there is no relationship between (admin,user) table. – Ganesh Oct 27 '12 at 09:36
@Jack, how do you say that two records in user and admin table are same? Is'nt ID enough for that comparison? Do you need to include domain_id as well? – Vikdor Oct 27 '12 at 09:38
I have modified question, there is no relation between admin and user so, we can not use u.id = a.id, there is relation between user and domain , admin and domain, i think here we need to create domain aliases to create relation between user and domain as well as admin and domain, is there any way to do it? – Ganesh Oct 27 '12 at 10:01
This answer is correct, except for one thing. The join condition doesn't agree with the original question, as edited. Jack's comment that there is no relationship between admin table and user table is incorrect. If there is no relationship then there is no way to determine the criterion for inclusion in the result. – Walter Mitty Oct 27 '12 at 10:13
Instead of (id, domain) tuple, it seems (email, domain) tuple is shared by admin and users table. Is that true? – Vikdor Oct 27 '12 at 10:15
@WalterMitty, the question has been edited after this response, as mentioned by the OP in the comment above yours. – Vikdor Oct 27 '12 at 10:16
@Vikdor - yes (email,domain_id) tuple shared by both admin and user tables. – Ganesh Oct 27 '12 at 10:21
Updated my response accordingly. – Vikdor Oct 27 '12 at 10:23
@Vikdor - but when we want output like (ddd@aaa.com), we need to join domain table to both admin and user tables. – Ganesh Oct 27 '12 at 10:37
@Jack, right. You need to concat the name column in domain and the email column in user to get the desired output. Updated my response. – Vikdor Oct 27 '12 at 11:06

Grisha Weintraub · Answer 2 · 2012-10-27T10:00:56.070

1

you can do it with not exists :

select u1.email+'@'+d1.name
from user u1 join domains d1 on u1.domain_id = d1.domain_id
where not exists 
(select 1 from admin where email = u1.email and domain_id = u1.domain_id)

edited Oct 27 '12 at 10:00

answered Oct 27 '12 at 09:25

Grisha Weintraub

7,803
1
25
45

2

FYI: This brings in the correlated subquery semantics (http://en.wikipedia.org/wiki/Correlated_subquery) increasing the cost of execution. – Vikdor Oct 27 '12 at 09:43
@Vikdor - I can't agree that the cost of 'not exists' is bigger than of 'left join'. Also it depends on RDBMS see for example - http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null – Grisha Weintraub Oct 27 '12 at 10:06
@Grisha - but in my case m using Mysql, so it takes more execution time. – Ganesh Oct 27 '12 at 10:14
@Jack - can you check how much time it takes with this query and compare it with other options ? – Grisha Weintraub Oct 27 '12 at 10:54

get distinct records without NOT IN query

2 Answers2