CSC 4402 20090930

CSC 4402 20090930 - With clause With clauses can be...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
CSC 4402 9/30/2009 Page 1 of 1 Conceptual procedure for evaluating: SELECT A 1 , A 2 , …, A k FROM T 1 , T 2 , …, T m WHERE Ρ Figure 1 – Relational algebra representation of query above 1. R T 1 X T 2 X … X T m 2. Assume R has n records: r 1 , r 2 , …, r n for i = 1,2, …,n test if r i satisfies condition Ρ if Ρ(r i ) = false/unknown => then remove r i call the result table R* 3. Project the A 1 , A 2 , …, A k attributes from R* as the final output Ask Dr.Chen about this: In general joins are better to use than subqueries. A subquery in a select clause is executed once per row that will be put in the result set. A subquery in where clause must be executed once per row that is processed; only rows that satisfy the subquery are kept. A subquery in a from clause must be executed once, and creates a temporary table that is removed after the query finishes.
Background image of page 1
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: With clause With clauses can be implemented using subqueries when the with clause is unavailable (i.e. when using MySQL). Heres an example that corresponds to slide 3.43: Select acct_num, balance from account where balance >= all (select balance from account); Views Table 1 Views vs. Base Tables Type Description Base Table Named table, real, autonomous Views Named table, virtual, derived tables Virtual tables do not keep a separate, permanent stored copy of the underlying tables data; it is generated each time the view is accessed. If you drop an underlying table, the view does not survive either. You can only update a view that is a column subset of a base table. Views that involve joins, aggregate functions, etc. cannot be updated....
View Full Document

This note was uploaded on 01/25/2010 for the course CSC 4402 taught by Professor Staff during the Fall '08 term at LSU.

Ask a homework question - tutors are online