1 / 11

Subqueries

Subqueries. Subquery. A subquery is an additional method for handling multitable manipulations. It is a SELECT statement that nests inside the WHERE, HAVING, or SELECT of another SELECT an INSERT, UPDATE or DELETE statement another subquery. General Syntax.

esthercollins
Télécharger la présentation

Subqueries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Subqueries

  2. Subquery A subquery is an additional method for handling multitable manipulations.It is a SELECT statement that nests inside • the WHERE, HAVING, or SELECT of another SELECT • an INSERT, UPDATE or DELETE statement • another subquery

  3. General Syntax SELECT colname FROM tableWHERE (SELECT colnameFROM table WHERE condition)ORDER BY colname • subqueries return results from an inner query to an outer clause

  4. Two Types of Subqueries • Noncorrelatedthe inner query is first evaluated and used in evaluating the outer query • Correlatedouter query provides values for the inner queries evaluation

  5. 3 types of results • Zero or more items (introduced with an IN or with a comparison operator modified by ANY or ALL) • single value (introduced with an unmodified comparison operator) • existence test (introduced with exists)

  6. Noncorrelated SELECT pub_nameFROM publishersWHERE pub_id in (SELECT pub_id FROM titles WHERE type=‘business’) • Subquery executes once and returns a list of values with which the outer query uses to finish its execution and evaluation

  7. Correlated SELECT pub_nameFROM publishers pWHERE ‘business’ IN (SELECT type FROM titles WHERE pub_id = p.pub_id) • The outer query executes first. • The inner query executes for every ROW the outer query returns and completes the evaluation.

  8. Joins or Subqueries? • When you are evaluating one table based on the aggregate analysis of another table - use a subquery • When you need to display and/or evaluate data from more than one table -use a join • Sometimes it is just a matter of preference

  9. Subquery Rules • The SELECT list of an inner subquery introduced with a comparator or IN can only have 1 expression or column name. • Subqueries introduced with EXISTS almost always have a select list of *. • Subqueries cannot manipulate their results. Cannot use ORDER BY or INTO.

  10. Comparators Commonly Used • IN, NOT IN • ANY, ALL • EXISTS, NOT EXISTS

  11. Formatting Text • getDate() - returns today’s date SELECT getDate() • char_length(data) - returns the length SELECT char_length(‘John’) • upper(data) - returns the uppercase • lower(data) - returns the lowercase • concatonation • SELECT (name +` `+address+`,`+state)

More Related