. SQL . SQL In

SQL In

SQL In Example One - In

Q: Who has been in the Austin Powers movies?

   
 Select Film_Title, Thespian_First_Name, Thespian_Last_Name
   From Thespian_Film_Table
  Where Film_Title  in ("Austin Powers: International Man of Mystery", 
                        "Austin Powers: The Spy Who Shagged Me");

   
 Austin Powers: International Man of Mystery,    Mike,       Myers
 Austin Powers: International Man of Mystery,    Elizabeth,  Hurley
 Austin Powers: International Man of Mystery,    Seth,       Greene
 Austin Powers: The Spy Who Shagged Me,          Mike,       Myers
 Austin Powers: The Spy Who Shagged Me,          Heather,    Graham
 Austin Powers: The Spy Who Shagged Me,          Seth,       Greene

Note: The above SQL is equivalent to

 Select Film_Title, Thespian_First_Name, Thespian_Last_Name
   From Thespian_Film_Table
  Where Film_Title  = "Austin Powers: International Man of Mystery"
     or Film_Title  = "Austin Powers: The Spy Who Shagged Me";

but using in is neater and easier to follow.

SQL In Example Two - In and Not In

Q: Who has been in the Austin Powers movies? But don't list anyone who's last name is "Graham" or "Greene".

   
 Select Film_Title, Thespian_First_Name, Thespian_Last_Name
   From Thespian_Film_Table
  Where Film_Title  in ("Austin Powers: International Man of Mystery", 
                        "Austin Powers: The Spy Who Shagged Me");
  and
  Where Thespian_Last_Name  not in ("Graham", "Greene");

   
 Austin Powers: International Man of Mystery,    Mike,       Myers
 Austin Powers: International Man of Mystery,    Elizabeth,  Hurley
 Austin Powers: The Spy Who Shagged Me,          Mike,       Myers

Special Thanks: to Joel Weight for the idea to show "not in".

Comments

to add the first comment for SQL In.