Home > mysql, Programming, Tip > Useful “IN” in mysql

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.

Advertisements
Categories: mysql, Programming, Tip
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: