SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Understanding SQL Subqueries: Types, Examples, and Differences from Joins

lara-kemp avatar
lara-kemp
21 diapositives · 373 Views · Nov 20, 2014

This guide explores SQL subqueries, which are SELECT statements nested within another SQL query to enhance data selection. We discuss various types, including scalar and correlated subqueries. Learn how to use subqueries effectively with examples such as finding authors who have written books or determining titles with greater than average YTD sales. Additionally, we compare subqueries to joins and explain the EXISTS predicate for checking row existence. Enhance your SQL skills by mastering these powerful techniques.

Understanding SQL Subqueries: Types, Examples, and Differences from Joins - slide 1
01 / 21Understanding SQL Subqueries: Types, Examples, and Differences from Joins
1 / 17

Télécharger la présentation

Understanding SQL Subqueries: Types, Examples, and Differences from Joins
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. Subqueries and Exists

  2. Subqueries • A subquery is a SELECT statement that is used to aid data selection by being nested within another SQL query • Are often used with in IN predicate • A subquery is a binary logical check, True or False

  3. Differences Between Subquery and Join • Subqueries are a primitive version of a join • Just like a join, a subquery matches the rows of one table to the rows of another table • Unlike a join, a subquery cannot return data from the inner table to the answer set

  4. Subquery Example • Find all authors who have written a book Select au_lname, au_fname From authors Where au_id in (select distinct au_id from titleauthor) • Go to Query Analyzer and run this query

  5. What Happens with a Subquery • In this case, the innermost query is resolved, building a list of values • The outer query is evaluated against that list like a normal IN predicate

  6. Scalar Subqueries • Find the book title having the lowest year to date sales • YTD Sales is in the TITLES table • We can get the lowest YTD Sales with a min function, but in doing that we lose the title (because we have to do some kind of grouping) • however

  7. Scalar Subquery Example • We can solve this with a subquery Select title From titles Where ytd_sales = ( select min(ytd_sales) from titles) • Go to Query Analyzer and try this

  8. Further Examples • We don’t have to use equality checks • See if you can modify your query to find all titles that have greater than average ytd_sales

  9. Greater than Average Sales Select title From titles Where ytd_sales > ( select avg(ytd_sales) from titles)

  10. Correlated Subquery • A correlated subquery is a further refinement of the subquery • It correlates or coordinates the two queries • The scope of the inner query becomes dependent on the row being looked at in the outer query

  11. Correlated Subquery Problem • Find all titles where the ytd sales for that title is greater than the average ytd sales for the publisher of the title • Notice in this problem we have to find the average ytd_sales for the publisher of a title. • That average number is dependent on the title we are testing • We need to coordinate the subquery with the outer query

  12. Correlated Subquery Example select title, ytd_sales from titles t1 where ytd_sales > ( Select avg(ytd_sales) from titles t2 where t1.pub_id = t2.pub_id) • Go to Query Analyzer and run this query Note the coordination Note the coordination

  13. Exists Predicate

  14. EXISTS • Specifies a subquery to test for the existence of rows. • Returns TRUEif a subquery contains any rows.

  15. Exists Problem • Find all Publishers that publish business books • A business book is determined by a type = ‘business’ in the titles table • Find the Publishers that have books with this type

  16. Exists Example SELECT DISTINCT pub_name FROM publishers p WHERE EXISTS (SELECT * FROM titles t WHERE t.pub_id = p.pub_id AND type = 'business') • Go to Query Analyzer and run this query

  17. What it does • Takes the value from Publishers for pub_id and checks Titles to see if that value is present for any row in the table. If there is and all other where conditions evaluate as true, then the subquery is true and the Exists requirement is met.

  18. Further Notes • Is a form or Correlated Subquery • The values returned are not important, the fact that a row (any row) is returned is what is important • The ‘NOT’ phrase can be used to check for a NOT EXISTS condition

  19. Exists Vs. Join • In some cases there may have a choice of using either an exist or do a join • Either technique may present a proper solution • Consider…

  20. Join Example Select distinct pub_name From publishers p inner join titles t on (t.pub_id = p.pub_id) Where type = 'business‘ • Which is better, Join or Exists?

  21. Exists Example Select distinct pub_name From publishers p Where exists ( Select 1 from titles t where p.pub_id = t.pub_id and type = ‘business’)