FInd broken relations in MySQL

Question

I have a question about relational databases. I know that when using this I should set my foreign keys properly but I didn't do that right away so now I have to clean up my messy database in order to remove entries with broken relations. My tables look a bit like this:

LINKS
| id | url | other, irrelevant fields |

LINK_USERS
| id | link_id | other, irrelevant fields |

It should be obvious that LINK_USERS has a relation with LINKS through the link_id field (which is the id of the entry in the LINKS table)

I've been messing around for a bit of time now and somehow I can't seem to select all rows from LINK_USERS that have no reference in LINKS.

I must add here that deleting should only have happened on the LINKS table. So what I would want from the query is to SELECT all rows FROM LINK_USERS WHERE link_id cannot be found in the LINKS table.

As you might have figured, I am no MySQL guru. If my question is vague let me know and I'll try to formulate it better.

Thanks in advance!

Answer 1

Your question is not vague at all; you can get what you want with very simple SQL (which looks very much like your English language description of what you're looking for):

 SELECT * FROM LINK_USERS WHERE link_id NOT IN (SELECT id FROM LINKS);

Answer 2

Here's the LEFT JOIN version:

SELECT *
FROM LINK_USERS
LEFT JOIN LINKS ON LINK_USERS.link_id = LINKS.link_id
WHERE LINKS.link_id IS NULL

It does the same thing, but it might be quicker on bigger tables. YMMV.