How can I don't use sub query in checking the missing data

Question

I have to write a query that find missing data, but I want it to write without any subqueries or nested SELECT (it finds the missing data and inserts it from a table to the one that has missing data).

The query is:

INSERT INTO Personal_tbl 
    SELECT * 
    FROM PersonalView 
    WHERE PID NOT IN (SELECT PID FROM Personal_tbl)

Answer 1

You would generally use not exists for such task:

insert into personal_tbl (...) -- enumerate the target columns here
select *                       -- and here as well
from personalview pv
where not exists (select 1 from personal_tbl pt where pt.pid = pv.pid)

I don't see why you wouldn't want to use a subquery for this. With an index on personal_tbl(pid), this should be an efficient approach. But if you like, you can implement the same logic with a left join:

insert into personal_tbl (...)
select pv.*
from personalview pv
left join personal_tbl pt on pt.pid = pv.pid
where pt.pid is null

Answer 2

INSERT INTO Personal_tbl
SELECT Pv.*
FROM PersonalView Pv
INNER JOIN Personal_tbl pt ON  pt.PID <> Pv.PID