B7uqaDeg

1DROP TABLE IF EXISTS main_table;
2DROP TABLE IF EXISTS user_table;
3CREATE TABLE IF NOT EXISTS main_table (
4    id INTEGER PRIMARY KEY, 
5    user_reference INTEGER -- WOULD/COULD have REFERENCES usertable (id)
6);
7CREATE TABLE IF NOT EXISTS user_table (id INTEGER PRIMARY KEY, user_name TEXT);
8INSERT INTO user_table VALUES
9    (null,'Fred'),
10    (null,'Bert'),
11    (null,'Tom')
12;
13INSERT INTO main_table VALUES
14    (null,1), -- References Fred (most likely)
15    (null,'not a valid reference'), -- oooops this will not reference a user
16    (null,2) -- References Bert (most likely)
17;
18SELECT * FROM main_table WHERE user_reference NOT IN (SELECT id FROM user_table);