Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

SQL: WHERE Subqueries and Other Syntactic Sugar, Slides of Introduction to Database Management Systems

This document, authored by david toman from the university of waterloo, provides an introduction to sql syntax sugar, focusing on the use of where subqueries. Various types of esoteric predicates, such as range searches and string matches, and explains how to use subqueries to define more complex search conditions. The document also discusses the advantages and disadvantages of using where subqueries and provides examples of their usage.

Typology: Slides

2011/2012

Uploaded on 02/12/2012

richiey
richiey 🇨🇦

32 documents

1 / 17

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
SQL: WHERE Subqueries
and Other Syntactic Sugar
David Toman
School of Computer Science
University of Waterloo
Introduction to Databases CS348
David Toman (University of Waterloo) SQL Syntax Sugar 1 / 17
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff

Partial preview of the text

Download SQL: WHERE Subqueries and Other Syntactic Sugar and more Slides Introduction to Database Management Systems in PDF only on Docsity!

SQL: WHERE Subqueries

and Other Syntactic Sugar

David Toman

School of Computer Science University of Waterloo

Introduction to Databases CS

More on "WHERE" conditions

  • (^) Most queries are SELECT BLOCK queries.
  • (^) Early SQL developments tried to cram everything into the SELECT block

1 Esoteric predicates (atomic)

  • (^) range searches (for ordered types) Name BETWEEN ’C’ AND ’EZ’
  • (^) “almost” matches (for strings) Name LIKE ’A%’ "" matches one character "%" matches any number of characters 2 Predicates defined by subqueries ) allow for really tricky formulation of queries.

WHERE Subqueries

  • Additional (complex) search conditions ) query-based search predicates
  • (^) Advantages
    • (^) simplifies writing queries with negation
  • Drawbacks
    • complicated semantics (especially when duplicates are involved)
    • (^) very easy to make mistakes
  • (^) VERY COMMONLY used to formulate queries

Overview of WHERE Subqueries

  • presence/absence of a single value in a query Attr IN ( Q ) Attr NOT IN ( Q )
  • relationship of a value to some/all values in a query Attr op SOME ( Q ) Attr op ALL ( Q )
  • (^) emptiness/non-emptiness of a query EXISTS ( Q ) NOT EXISTS ( Q )

In the first two cases Q has to be unary.

“Pure” SQL Equivalence

Nesting in the WHERE clause is mere syntactic sugar:

SELECT r.b SELECT r.b FROM r FROM r, ( WHERE r.a IN ( SELECT DISTINCT b SELECT b FROM s FROM s ) s ) WHERE r.a=s.b

All of the remaining constructs can be rewritten in similar fashion...

Example: "attr NOT IN ( Q )"

All author-publication ids for all publications except books and journals: SQL> select * 2 from wrote 3 where publication not in ( 4 ( select pubid from book ) 5 union 6 ( select pubid from journal ) )

AUTHOR PUBLICAT


1 ChTo 1 ChTo98a 1 Tom 2 ChTo 2 ChTo98a

... search conditions may contain complex queries.

Example: "attr op SOME/ALL ( Q )"

Find article with most pages: SQL> select pubid 2 from article 3 where endpage-startpage>=all ( 4 select endpage-startpage 5 from article 6 )

PUBID

ChTo

... another way of saying max attr = SOME (Q) is the same as attr IN (Q) attr <> ALL (Q) is the same as attr NOT IN (Q)

Parametric Subqueries

  • so far subqueries were independent on the main query ) not correlated ) not much fun (good only for simple queries)
  • SQL allows parametric (correlated) subqueries: ) of the form Q(p1,...pk) where pis are attributes in the main query. ) The truth of a predicate defined by a subquery is determined for each substitution (tuple) in the main query 1 instantiate all the parameters and 2 check for the truth value as before...

Example: NOT EXISTS

... and now it is easy to complement conditions:

SQL> select * 2 from wrote r 3 where not exists ( 4 select * 5 from wrote s 6 where r.publication=s.publication 7 and r.author<>s.author 8 )

AUTHOR PUBLICAT


1 Tom

Example: IN

SQL> select * 2 from wrote r 3 where publication in ( 4 select publication 5 from wrote s 6 where r.author<>s.author 7 )

AUTHOR PUBLICAT


1 ChTo 1 ChTo98a 2 ChTo 2 ChTo98a 2 ChSa 3 ChSa

Example

List all authors who always publish with someone else: SQL> select a1.name, a2.name 2 from author a1, author a 3 where not exists ( 4 select * 5 from publication p, wrote w 6 where p.pubid=w1.publication 7 and a1.aid=w1.author 8 and a2.aid not in ( 9 select author 10 from wrote 11 where publication=p.pubid 12 and author<>a1.aid 13 ) 14 )

Summary

  • WHERE subqueries—easy formulation of queries of the form “All x in R such that (a part of) x doesn’t appear in S”.
  • (^) subqueries only stand for WHERE conditions ... CANNOT be used to produce results
  • you can use input parameters but these must be bound in the main query
  • all of these are just a syntactic sugar and can be expressed using queries nested in the FROM clause - (^) but it might be quite hard... - easy to make mistakes though (be very careful)