Including multiple columns in NOT IN-CodePudding

I have two tables as below.

Table 1

Book price
A   100
B   200
C   400
D   300

Table 2

Book price
A   100
B   200
C   400

Now I am executing below command as I want only the 4th record to get inserted into table 2. I want to add both the column names before NOT IN. what should I do?

Insert into table2 select * from table1 t1 where t1.book not in (select book from table2);

CodePudding user response：

You can use NOT EXISTS along with matching the presumably primary key columns Book for both of the tables such as

INSERT INTO table2
SELECT * 
  FROM table1 t1 
 WHERE NOT EXISTS ( SELECT 0 FROM table2 WHERE Book=t1.Book);