Useful “IN” in mysql
When using IN in mysql statement, the number of columns after the keyword WHERE can be more than one, for example,
SELECT * from student_info WHERE (native_language, interest) in ( ('English', 'singing'), ('Spanish', 'dancing'), ('Franch', 'movie') )
The same technique can be used to compare two tables with the same primary (or unique index) keys that are formed by multiple columns (also known as the composite key). For example:
SELECT * from tbl_A WHERE ( student_id, last_name, ssn4 ) NOT IN ( SELECT student_id, last_name, ssn4 FROM tbl_B )
Note: Columns student_id, last_name, ssn4 form the primary (or unique index) key in both table tbl_A and tbl_B, the column names don’t have to be the same in tbl_B though, as long as each corresponding column holds the same type of data as the other table.
Rows that don’t have the same primary keys can be easily spotted by simply swapping the table names (and adjust the column names if different) in the above example, if necessary.