CODEWITHSUNDEEP
mysqljoin
thrashr888
thrashr888

Reputation: 1477

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

For example, I have a pet table and a lost pets table. I want to get all of the pets that are NOT lost with 1 join and no sub-selects. Is that possible? A typical join only returns results that are in both tables.

Upvotes: 3

Views: 3047

Answers (4)

mat
mat

Reputation: 13353

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;

Upvotes: 2

Scott Bevington
Scott Bevington

Reputation: 1201

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;

Upvotes: 3

eulerfx
eulerfx

Reputation: 37739

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

Upvotes: -1

dkretz
dkretz

Reputation: 37655

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

Upvotes: 7

Related Questions