. SQL . SQL Subselect

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

to add the first comment for SQL Subselect.