Testing for NULL o The condition value =NULL always evaluates to UNKNOWN,even if the value is NULL! Use value IS NULL or value IS NOT NULL instead. Null is a special value,while unknown is a truth-value,like true or false,is a result of the comparison,or evaluation on a condition
Testing for NULL z The condition value = NULL always evaluates to UNKNOW N, even if the value is N ULL! z Use value IS NULL or value IS NOT N ULL instead. Null is a special value, while unknown is a truth-value, like true or false, is a result of the comparison, or evaluation on a condition
Classroom exercise Select From Movie Where length <=120 or length >120; Which behaves unintuitively when the length of a movie is NULL.Find a simpler equivalent query,one with a single condition in the WHERE clause. Where Length IS NOT NULL
Classroom exercise Select * From Movie Where length <=120 or length >120; Which behaves unintuitively when the length of a movie is NULL.Find a simpler equivalent query, one with a single condition in the WHERE clause. Where Length IS NOT NULL Where Length IS NOT NULL
Multi-relation Queries Interesting queries often combine data from more than one relation. List of relations in FROM clause. Relation-dot-attribute disambiguates attributes from several relations. Example:Find the beers that the frequenters of Joe's Bar like. Likes (drinker,beer) Frequents(drinker,bar) SELECT beer FROM Frequents,Likes WHERE bar oe''s Bar'AND Frequents.drinker Likes.drinker;
Multi-relation Queries Interesting queries often combine data from more than one relation. z List of relations in FROM cl a u s e. z Relation-dot-attribute disambiguates attributes from several relations. Example: Find the beers that the frequenters of Joe's Bar like. Li kes( drink er,be er) F requ e nt s ( drinker, b ar) SE L E C T b e er FROM Fre q u e nt s ,Li k e s W HER E b ar ='Jo e''s B ar' AND F r e que nt s . drink er = Li ke s . drink er ;
Formal Semantics Almost the same as for single- relation queries: 1.Start with the product of all the relations in the FROM clause. 2.Apply the selection condition from the WHERE clause. 3.Project onto the list of attributes and expressions in the SELECT clause
Formal Semantics z Almost the same as for singlerelation queries: 1. Start with the product of all the relations in the FROM clause. 2. Apply the selection condition from the WHERE clause. 3. Project onto the list of attributes and expressions in the SELECT clause
Operational Semantics Imagine one tuple-variable for each relation in the FROM clause. These tuple-variables visit each combination of tuples,one from each relation. If the tuple-variables are pointing i to tuples that satisfy the WHERE clause,send these tuples to the SELECT clause
Operational Semantics zImagine one tuple-variable for each relation in the FROM clause. – These tuple-variables visit each combination of tuples, one from each relation. zIf the tuple-variables are pointing to tuples that satisfy the WHERE clause, send these tuples to the SELECT clause