---

Converting an SQL command with sub-query using a 2-argument selection

---

---

---

 

Slideshow:

Prelude: the 2-argument selection operator
 
Prelude: the 2-argument selection operator
 
How to use the 2-argument selection operator
Given a SQL query that contains a sub-query

Translate the sub-query first as:

How to use the 2-argument selection operator
Given a SQL query that contains a sub-query

Then translate the outer query using the 2-argument selection (that contains a relation !):

Example: converting nested SQL query into an (initial) logical query plan
The nested SQL query:

Example: converting nested SQL query into an (initial) logical query plan
(1) Convert the inner query (using a ordinary selection (σ) operator):

Example: converting nested SQL query into an (initial) logical query plan
(2) Convert the outer query using a 2-argument selection (σ^*) operator):

What's next ahead ?
 

❮ ❯



---

---

---

---

• Prelude: the 2-argument selection (σ^*) operation
  • The Normal 1-argument selection operation:

    The 1-argument selection operation σc uses a condition c: Attributes         and/or          Constants/patterns

    ---

  • Example:

    σsalary > 50000 ( employee ) σyear LIKE '%1996' ( Movie )

    ---

    ---

  • The 2-argument selection operator σ^* :

    Syntax: Meaning: Select all tuples from the input relation R that satisfy the (more general form of) condition R-condition An R-condition can contain: Attributes                           and/or          Constants/patterns         and/or          Relations !!!

  ---

  ---

  ---

  ---

• Using a 2-argument selection σ^* to convert sub-queries
  • SQL Statement:

    SELECT .... FROM .... WHERE A IN (Sub-query)

    ---

  • How to convert an SQL with sub-query into a logical query plan (tree):

    First, convert the (inner) sub-query into a logical (Relational Alg) query plan: Convert the outer query using a 2-argument selection as follows:

  ---

  ---

  ---

  ---

• Example: converting a sub-query
  • SQL query:

    ---

  • Convert the inner query using the no-subquery conversion technique:

    ---

  • Convert the outer query using a 2-argument selection:

  ---

  ---

  ---

  ---

• Comment....
  • I use a star * on σ^* to emphasize the difference between the 2 selection operations.

    ---

  • The text book does not use σ^* , but the ordinary selection operator symbol: σ

  ---

  ---

  ---

  ---

• Replacing a 2-argument selection by an 1-argument selection operation
  • The next step in the construction process of the initial query plan is:

    Replacing all 2-argument selection by an 1-argument selection operation

    ---

  • Main difficulty:

    Each form of the 2-argument selection has it own set of rules on re-writing.... I.e., there are different rules for: Uncorrelated sub-query Correlated sub-query