A subquery is a query nested inside another query.
Syntax:
-- Subquery
SELECT column_1, column_2
FROM table_1
WHERE column_1 > (SELECT column_2 FROM table_1);Example: Select the film id, title, and rental rate from the film table while the rental rate is greater than the selected average of the rental rate from the film table.
-- Select the film id, title, and rental rate from the film table while the rental rate is greater than the selected average of the rental rate from the film table.
SELECT film_id, title, rental_rate
FROM film
WHERE
rental_rate > (SELECT AVG(rental_rate) FROM film)
limit 20;## film_id title rental_rate
## 1 133 Chamber Italian 4.99
## 2 384 Grosse Wonderful 4.99
## 3 8 Airport Pollock 4.99
## 4 98 Bright Encounters 4.99
## 5 2 Ace Goldfinger 4.99
## 6 3 Adaptation Holes 2.99
## 7 4 Affair Prejudice 2.99
## 8 5 African Egg 2.99
## 9 6 Agent Truman 2.99
## 10 7 Airplane Sierra 4.99
## 11 9 Alabama Devil 2.99
## 12 10 Aladdin Calendar 4.99
## 13 13 Ali Forever 4.99
## 14 15 Alien Center 2.99
## 15 16 Alley Evolution 2.99
## 16 20 Amelie Hellfighters 4.99
## 17 21 American Circus 4.99
## 18 22 Amistad Midsummer 2.99
## 19 24 Analyze Hoosiers 2.99
## 20 25 Angels Life 2.99Example: Select the film id from the inventory ID and observe the return date between 2005-05-29 and 2005-05-30.
-- Select the film id and title from the inventory ID and observe the return date between 2005-05-29 and 2005-05-30.
SELECT film_id, title
FROM film
WHERE film_id IN (SELECT inventory.film_id
FROM rental
INNER JOIN inventory on inventory.inventory_id = rental.inventory_id
WHERE
return_date BETWEEN '2005-05-29' AND '2005-05-30')
;## film_id title
## 1 307 Fellowship Autumn
## 2 255 Driving Polish
## 3 388 Gunfight Moon
## 4 130 Celebrity Horn
## 5 563 Massacre Usual
## 6 397 Hanky October
## 7 898 Tourist Pelican
## 8 228 Detective Vision
## 9 347 Games Bowfinger
## 10 1000 Zorro Ark
## 11 624 Nightmare Chill
## 12 179 Conquerer Nuts
## 13 54 Banger Pinocchio
## 14 684 Pizza Jumanji
## 15 267 Eagles Panky
## 16 68 Betrayed Rear
## 17 868 Superfly Trip
## 18 138 Chariots Conspiracy
## 19 418 Hobbit Alien
## 20 681 Pirates Roxanne
## 21 52 Ballroom Mockingbird
## 22 83 Blues Instinct
## 23 858 Submarine Bed
## 24 45 Attraction Newton
## 25 958 Wardrobe Phantom
## 26 783 Shane Darkness
## 27 295 Expendable Stallion
## 28 694 Prejudice Oleander
## 29 484 Jerk Paycheck
## 30 963 Watch Tracy
## 31 233 Disciple Mother
## 32 299 Factory Dragon
## 33 471 Island Exorcist
## 34 89 Borrowers Bedazzled
## 35 241 Donnie Alley
## 36 393 Halloween Nuts
## 37 776 Secret Groundhog
## 38 971 Whale Bikini
## 39 348 Gandhi Kwai
## 40 967 Weekend Personal
## 41 870 Swarm Gold
## 42 50 Baked Cleopatra
## 43 941 Videotape Arsenic
## 44 300 Falcon Volume
## 45 953 Wait Cider
## 46 487 Jingle Sagebrush
## 47 227 Details Packer
## 48 838 Stagecoach Armageddon
## 49 200 Curtain Videotape
## 50 73 Bingo Talented
## 51 630 Notting Speakeasy
## 52 505 Labyrinth League
## 53 103 Bucket Brotherhood
## 54 715 Range Moonwalker
## 55 902 Trading Pinocchio
## 56 875 Talented Homicide
## 57 772 Sea Virgin
## 58 527 Lola Agent
## 59 257 Drumline Cyclone
## 60 480 Jeepers Wedding
## 61 247 Downhill Enough
## 62 651 Packer Madigan
## 63 120 Caribbean Liberty
## 64 973 Wife Turn
## 65 757 Sagebrush Clueless
## 66 15 Alien Center
## 67 139 Chasing Fight
## 68 226 Destiny Saturday
## 69 349 Gangs Pride
## 70 164 Coast Rainbow
## 71 914 Trouble Date
## 72 248 Dozen Lion
## 73 948 Voice Peach
## 74 293 Exorcist Sting
## 75 573 Microcosmos Paradise
## 76 19 Amadeus Holy
## 77 288 Escape Metropolis
## 78 234 Disturbing Scarface
## 79 506 Lady Stage
## 80 621 Network Peak
## 81 865 Sunrise League
## 82 517 Lesson Cleopatra