Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this table structure:

 id1 id2
 1   2 
 1   3
 1   4
 2   1
 2   5

I need to build a SQL to select id2 where id1 is not in id2. For example if id1 = 1 then only id2=3 and id2=4. Any help? I try with this one:

SET @a=1;
SELECT DISTINCT x.id2 
 FROM tt x, tt y 
 WHERE x.id1=@a AND x.id1 != y.id2;

but itsn't working

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted
SET @a=1;
SELECT x.id2
FROM (SELECT * FROM tt WHERE id1=@a) x LEFT JOIN tt y
ON x.id2 = y.id1
WHERE y.id1 IS NULL;

I tried it against my query and it works

mysql> select * from tt;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    2 |    1 |
|    2 |    5 |
+------+------+
5 rows in set (0.00 sec)

mysql> SET @a=1;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT x.id2
    -> FROM (SELECT * FROM tt WHERE id1=@a) x LEFT JOIN tt y
    -> ON x.id2 = y.id1
    -> WHERE y.id1 IS NULL;
+------+
| id2  |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.00 sec)

mysql>

UPDATE 2012-04-18 11:58 EDT

You can take the entire query and

  • make it a subquery
  • LEFT JOIN the subquery to the users, groups, and profiles tables

maybe something like this

SET @a=1;
SELECT u.*,g.*,p.* FROM
(
    SELECT x.id2 user_id
    FROM (SELECT * FROM tt WHERE id1=@a) x LEFT JOIN tt y
    ON x.id2 = y.id1
    WHERE y.id1 IS NULL
) keys
LEFT JOIN users u USING (user_id)
LEFT JOIN groups g USING (user_id)
LEFT JOIN profiles p USING (user_id)
;
share|improve this answer
    
+1 The sub-select might speed things up a bit (compared to the raw LEFT JOIN version). Or is the planner smart enough to avoid unnecessary JOIN operations all by himself? –  Erwin Brandstetter Apr 17 '12 at 22:13
1  
@Erwin Subselects are good as long as MySQL uses indexes and does not make rows disappear while optimizing : dba.stackexchange.com/a/1384/877 –  RolandoMySQLDBA Apr 17 '12 at 22:18
1  
@Erwin For SELECT queries, the MySQL Query Optimizer is smart enough to avoid unnecessary JOIN operations. I still prefer refactoring queries if the explain plan isn't messier after the refactor. –  RolandoMySQLDBA Apr 17 '12 at 22:23
    
That's a pretty interesting link. A bit disturbing, but good to know. And thanks for the update on the query optimizer! –  Erwin Brandstetter Apr 17 '12 at 22:25
add comment

There are basically three techniques to do what you are looking for:

NOT EXISTS:

SELECT DISTINCT x.id2 
FROM   tt x
WHERE  x.id1 = @a
AND NOT EXISTS (
    SELECT *
    FROM   tt y
    WHERE  y.id1 = x.id2
    );

LEFT JOIN / IS NULL:

SELECT DISTINCT x.id2 
FROM   tt x
LEFT   JOIN tt y ON y.id1 = x.id2
WHERE  x.id1 = @a
AND    y.id1 IS NULL

NOT IN:

SELECT DISTINCT x.id2 
FROM   tt x
WHERE  x.id1 = @a
AND    x.id2 NOT IN (
    SELECT DISTINCT id1
    FROM   tt y
    );

You'll have to test which one is fastest for you. Benchmarks disagree. It depends on data distribution and other details. NOT IN rarely wins. It's mostly one of the first two.

As @ypercube commented: if (id1, id2) is unique, the DISTINCT clause is not needed - except the one in the NOT IN sub-query, which is meant to help performance.

share|improve this answer
    
I tried it and it works. +1 for getting it first !!! –  RolandoMySQLDBA Apr 17 '12 at 22:09
    
Second one works !!! –  RolandoMySQLDBA Apr 17 '12 at 22:14
1  
If (id1, id2) combination is unique, then we can skip the DISTINCT –  ypercube Apr 17 '12 at 22:17
    
@ypercube: indeed we can (except for the one in the NOT IN sub-select). But that condition has not been declared in the question. –  Erwin Brandstetter Apr 17 '12 at 22:19
1  
An observation. If (id1, id2) combination is not unique, then there are 2 more variations. Changing versions 1 (or 3) by removing the DISTINCT, adding GROUP BY x.id2 and moving the NOT EXISTS (or NOT IN) condition to HAVING clause. This may produce different execution plans. –  ypercube Apr 17 '12 at 23:05
show 6 more comments

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.