Find broken relationship in mysql

Question

I have a question about relational databases. I know that when using this, I have to correctly set my foreign keys, but I did not do it right away, so now I need to clear my dirty database in order to delete records with broken relationships. My tables look something like this:

LINKS
| id | URL | other irrelevant fields |

LINK_USERS
| id | link_id | other irrelevant fields |

It should be obvious that LINK_USERS is related to LINKS through the link_id field (which is the identifier of the entry in the LINKS table)

I have already mixed up a bit, and somehow I can’t select all the lines from LINK_USERS that do not have a link in LINKS.

I must add here that deletion should only occur in the LINKS table. So, what would I want from a query: SELECT all rows FROM LINK_USERS WHERE link_id could not be found in the LINKS table.

As you might think, I'm not a MySQL guru. If my question is vague, let me know and I will try to formulate it better.

Thanks in advance!

Answer 1

Your question is not vague; you can get what you want with very simple SQL (which is very similar to your English description of what you are looking for):

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

Answer 2

Here is the version of LEFT JOIN:

 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, but can be faster on large tables. YMMV.