SQL Subselect

SQL Subselect Example One

Q: What Harrison Ford movies were made in 1984?

   
 Select Film_Title
   From Thespian_Film_Table
  Where Thespian_Last_Name  = "Ford"
    and Thespian_First_Name = "Harrison"
    and Film_Title in
         (Select Film_Title
            From Film_Table
            Where Film_Year equals "1984");

   
 Indiana Jones and the Temple of Doom

Note: The first select gets all Harrison Ford movies. The second select gets all movies made in 1984. Combined the selects get all Harrison Ford movies made in 1984.

SQL Subselect Example Two - Not In

Q: What Harrison Ford movies were not made in 1984?

   
 Select Film_Title
   From Thespian_Film_Table
  Where Thespian_Last_Name  = "Ford"
    and Thespian_First_Name = "Harrison"
    and Film_Title not in
         (Select Film_Title
            From Film_Table
           Where Film_Year equals "1984");

   
 Air Force One
 American Graffiti
 Apocalypse Now
 Blade Runner
 Clear and Present Danger
 Frantic
 Hanover Street
 Heroes
 Indiana Jones and the Last Crusade
 Patriot Games
 Presumed Innocent
 Raiders of the Lost Ark
 Regarding Henry
 Return of the Jedi
 Six Days Seven Nights
 Star Wars
 The Empire Strikes Back
 The Frisco Kid
 The Fugitive
 The Mosquito Coast
 Working Girl

Note: The first select gets all Harrison Ford movies. The second select gets all movies made in 1984. Combined with a not the selects get all Harrison Ford movies not made in 1984.

SQL Subselect Example Three - A Correlated Subquery

Q: What movies has Harrison Ford acted in with George Lucas directing?

   
 Select Thespian_Film_Table.Film_Title
   From Thespian_Film_Table
  Where Thespian_Last_Name  = "Ford"
    and Thespian_First_Name = "Harrison"
    and exists
         (Select *
            From Director_Film_Table
           Where Director_Last_Name  = "Lucas"
             and Director_First_Name = "George"
             and Director_Film_Table.Film_Title 
               = Thespian_Film_Table.Film_Title);

   
 American Graffiti
 Star Wars

Note: A correlated subquery directly ties the table nested in the subquery back to a table in the query.
The first select gets all Harrison Ford movies. The second select gets all Harrison Ford movies Directed by George Lucas. The statement "and Director_Film_Table.Film_Title = Thespian_Film_Table.Film_Title" in the subselect is where both tables are "tied together" or correlated.

Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.

Comment by TheMadProfessor on 2013-05-22 Rate this Comment

A couple of comments regarding IN/EXISTS:

1) While the two appear basically interchangeable in usage in the samples above, they have a fundamental difference regarding fields that are nullable with some DBMSs (Oracle for one). Since IN is comparing discrete values, a field with a NULL value will never be selected, which may or may not be appropriate for the application. With EXISTS, the actual value of the field is irrelevant, just whether or not the row as a whole is there.

2) While there is nothing logically wrong with the sample of EXISTS above, it can be made more efficient to run. By doing a "SELECT *" in the subselect, you are passing all of a row's data to the subselect result set when all you really want to know is whether the row is there or not. Instead, if you specify "SELECT 1" in the subselect, no actual table data is passed, just an INT literal. In an application with large tables, this can result in significant processing time savings. By minimizing the subselect result table, computer CPU and memory usage is also minimized since you don't get one (or more) complete table rows in the result set.