ansaurus

Question

Answer 1

+4 A:

You're describing an OUTER JOIN as compared to a standard INNER JOIN. Google or check your documentation - I'm sure you'll find lots of examples. :)

SELECT * FROM pets AS p
LEFT OUTER JOIN lost-pets AS lp
ON p.name = lp.name
WHERE lp.id IS NULL

le dorfier 2008-12-15 22:04:13

Answer 2

+1 A:

It is possible, yes, say :

SELECT *
FROM pets LEFT OUTER JOIN pets-lost ON pets.id = pets-lost.id
WHERE pets-lost.id IS NULL;

mat 2008-12-15 22:05:01

Answer 3

A:

Why not do where not exists (select * from Lost ...)? Its a sub-select, but I don't see why thats a problem.

eulerfx 2008-12-15 22:05:12

one problem is that e.g. old versions of mysql don't support subselects.

le dorfier 2008-12-15 22:06:23

one other problem, is that subselects are much more slow than joins.

mat 2008-12-15 22:07:41

one additional problem is that the question asked how to do it without sub selects

Dave L. 2008-12-15 22:09:59

@mat: "subselects are much more slow than joins" not always true. You need to actually measure. Some subselects are optimized into joins.

S.Lott 2008-12-15 22:14:59

The better the query optimizer, the less it matters. However, some are dumb indeed.

Zan Lynx 2008-12-16 02:52:28

Answer 4

+2 A:

SELECT PETS.NAME
FROM PETS
   LEFT OUTER JOIN LOST_PETS
     ON PETS.PET_ID = LOST_PETS.PET_ID
WHERE LOST_PETS.PET_ID IS NULL;

Scott Bevington 2008-12-15 22:07:10

How do you JOIN two MySQL tables where the data is NOT in the other table?