Finding pairs that do not exist in a different table

Question

I have a table (orders) with order id, location 1, location 2 and another table (mileage) with location 1 and location 2.

I'm using the Except action to return those location pairs in orders that are not in mileage. But I'm not sure how I can also return the corresponding order_id that belongs to those pairs (order_id doesn't exist in the mileage table). The only thing I can think of is having an outer select statement that searches orders for those location pairs. I haven't tried it but I'm looking for other options.

I have something like this.

SELECT location_id1, location_id2  
FROM orders 
except
SELECT lm.origin_id, lm.dest_id
from mileage

How can I also retrieve the order id for those pairs?

Is either of the columns nullable, and will it contain null values? Doesn't seem likely, in which case Thomas's answer is correct. — RichardTheKiwi, Mar 28 '11 at 19:52

score 24 · Accepted Answer · answered Mar 28 '11 at 19:43

24

You might try using a Not Exists statement instead:

Select O.order_id, O.location_id1, O.location_id2
From orders As O
Where Not Exists    (
                    Select 1
                    From mileage As M1
                    Where M1.origin_id = O.location_id1
                        And M1.dest_id = O.location_id2
                    )

Another solution if you really wanted to use Except

Select O.order_id, O.location_id1, O.location_id2
From Orders As O
Except
Select O.order_id, O.location_id1, O.location_id2
From Orders As O
    Join Mileage As M
        On M.origin_id = O.location_id1
            And M.dest_id = O.location_id2

answered Mar 28 '11 at 19:43

Thomas

63,911
12
95
141

+1 - This will also most likely run quicker since the engine will only check for the presence of a record in the subquery instead of executing it and comparing result sets like the `EXCEPT` – JNK Mar 28 '11 at 19:46
2

@JNK - Agreed. I'd generally use the Exists version over Except in this case. Still, if he had some reason to use Except, I provided an alternate form that would do the trick. – Thomas Mar 28 '11 at 19:47
+1 for providing the 'better' solution in addition to what the OP was expecting. Sometimes it's easier to use a poor query than explain to micromanagement why you didn't use the feature they thought you should use! – corsiKa Mar 28 '11 at 19:49
1

Doesn't consider NULLs. Should be a note to that effect – RichardTheKiwi Mar 28 '11 at 19:52

score 3 · Answer 2 · answered Mar 28 '11 at 19:46

You could left-outer-join to the mileage table, and only return rows that don't join. Like so:

select O.order_id, O.location_id1, O.location_id2
from orders O left outer join mileage M1 on
  O.location_id1 = M1.origin_id and
  O.location_id2 = M1.dest_id
where M1.origin_id is NULL

score 2 · Answer 3 · answered Mar 28 '11 at 19:43

If you want to get the pairs that do not exist on mileage table you can do something like

select location_id1, location_id2
from orders
where (select count(*) from mileage 
       where mileage.origin_id = location_id1 and mileage.dest_id = location_id2) = 0

ypercubeᵀᴹ · Answer 4 · 2011-03-28T21:26:10.327

2

I thought it was but as Gabe pointed, this does NOT work in SQL-Server 2008:

SELECT order_id
     , location_id1
     , location_id2  
FROM orders 
WHERE (location_id1, location_id2) NOT IN
  ( SELECT origin_id, dest_id
    FROM mileage
  )

Would this solution with EXCEPT (which actually is a JOIN between your original query and Orders) work fast or horribly? I have no idea.

SELECT o.order_id, o.location_id1, o.location_id2  
FROM orders o
  JOIN
    ( SELECT location_id1, location_id2  
      FROM orders 
      except
      SELECT origin_id, dest_id
      FROM mileage
    ) AS g
    ON o.location_id1 = g.location_id1
    AND o.location_id2 = g.location_id2

edited Mar 28 '11 at 21:26

answered Mar 28 '11 at 20:47

ypercubeᵀᴹ

113,259
19
174
235

Nope, only one column can be selected when using IN/NOT IN. – Gabe Mar 28 '11 at 20:56
@Gabe: I didn't know that, thnx. – ypercubeᵀᴹ Mar 28 '11 at 21:09

score 0 · Answer 5 · answered Jul 30 '11 at 21:00

0

MySQL doesn't support Except. For anyone who comes across this question using MySQL, here's how you do it:

http://nimal.info/blog/2007/intersection-and-set-difference-in-mysql-a-workaround-for-except/

answered Jul 30 '11 at 21:00

James Kingsbery

7,298
2
38
67

Finding pairs that do not exist in a different table

5 Answers5