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 query to select id2 where id1 is not in id2. For example if id1 = 1 then only id2=3 and id2=4.
I tried this one:

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

but it's not 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

For the simple case of a single given id1:

SELECT DISTINCT x.id2   -- DISTINCT may not be needed if there are no dupes
FROM   tbl x
WHERE  x.id1 =  @a
AND    x.id2 <> @a;

Generally, there are basically four techniques to do what you are after:

NOT EXISTS

SELECT DISTINCT x.id2 
FROM   tbl x
WHERE  <some condition>
AND    NOT EXISTS (
    SELECT 1
    FROM   tbl y
    WHERE  <some condition>
  AND    y.id1 = x.id2
    );

LEFT JOIN / IS NULL

SELECT DISTINCT x.id2 
FROM   tbl x
LEFT   JOIN tbl y ON y.id1 = x.id2
                 AND <some condition for y>
WHERE  <some condition for x>
AND    y.id1 IS NULL

EXCEPT

SELECT DISTINCT id2
FROM   tbl
WHERE  <some condition>

EXCEPT ALL            -- ALL to make it faster - no dupes left after DISTINCT
SELECT tbl.id1
FROM   tbl
WHERE  <some condition>

NOT IN

SELECT DISTINCT x.id2 
FROM   tbl x
WHERE  <some condition>
AND    x.id2 NOT IN (
    SELECT DISTINCT id1
    FROM   tbl y
    WHERE  <some condition>
    );

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.

Similar answer with more details for PostgreSQL on SO.

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.