There is a table 1

id | val -------- 1 | 100 -------- 2 | 101 -------- 3 | 100 -------- 4 | 105

and table 2

 id | mainid | val ----------------- 1 | 1 | 10 ----------------- 2 | 2 | 11 ----------------- 3 | 4 | 10

It is necessary to choose from table 1 records that are not in table 2, where mainid is the id of the record from the first table. Here the result will be id 3, since in the second table there is no row with mainid 3. How to create a query?

    2 answers 2

     SELECT * FROM table1 WHERE id NOT IN (SELECT mainid FROM table2);

      To perform the subtraction operation, I recommend using left join and filtering by the join field:

       select t1.* from table1 as t1 left join table2 as t2 on t1.id = t2.mainin where t1.id is null