Subqueries

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
  typebusiness)
• 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 todays 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)