[thelist] SQL command help

[rudy]

> Hopefully the real DB gurus will only be one or two into
> their Sunday brews if what I suggested fails.


i usualyy stop countng after three (hic) when the nfl is on

;o)

chris, matt's way does work -- use an outer join and check for null

here's your original join query, using A and B as table names so that they
stand out a bit more --

   select A.user_id, B.id
      from A, B
      where A.user_id = B.id

your question was to find the row that's missing from A

matt's suggestion was

  select A.user_id
      from B right join A
        on A.user_id = B.id
    where B.id is null

matt is definitely on the right track, he just got the tables backwards
;o)

if you want to find the row that's missing from A, you need to return the
key from B that doesn't match any row from A, in other words you have to
return the B.id field

as to whether you should say "A left join B" or "B right join A" this
depends on which table you want "all the rows" from

by definition, an outer join gets all the rows from one table, plus any
matching rows from the other table, with nulls in the columns where no
matching row was found

therefore if you are looking for rows of B that don't have matching rows of
A -- which was your original question, chris -- then you need "B left join
A"

notice that it is the left or right "direction" which "points at" the table
that you want "all the rows" from

so you can also say "A right join B" which is the same as "B left join
A" -- get it?


anyhow, there is an even better way   ;o)

chris wanted *only the missing* rows, so a NOT EXISTS query is perfect

  select id
     from B
    where
       NOT EXISTS
         (select 1 from A
            where userid = B.id)

the nice part about the NOT EXISTS syntax is that it is easier to
understand -- what is *not * required is isolated -- whereas the full outer
join with null check is more, um, subtle as to what you are really after

also, the database optimizer can do stuff like look in an index and see a
record id and therefore *not retrieve* the actual row -- so NOT EXISTS
syntax executes better



the west coast games should be well into the first quarter, so we now
return you to your regularly scheduled beer...


rudy