SQL SubSelect

0

Posted in ,

I came across this examples online when I am looking for a solution in selecting data from database that starts with numbers only.

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.

Reactions: 

Comments (0)