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

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)