Represent a subquery in relational algebra

sql relational-algebra
AnEventHorizon picture AnEventHorizon · Oct 3, 2010 · Viewed 10.6k times · Source

How do I represent a subquery in relation algebra? Do I put the new select under the previous select condition?

SELECT number
FROM collection
WHERE number = (SELECT anotherNumber FROM anotherStack);

Answer

Martin Smith picture Martin Smith · Oct 5, 2010

You would just rewrite that as a join.

I'm not sure how widely used the syntax I learned for Relational Algebra is so in words.

  1. Take a projection of anotherNumber from anotherStack
  2. Rename anotherNumber from the result of step 1 as number
  3. Natural Join the result of step 2 onto collection
  4. Take a final projection of number from the result of step 3

Related questions

Difference between a theta join, equijoin and natural join

I'm having trouble understanding relational algebra when it comes to theta joins, equijoins and natural joins. Could someone please help me better understand it? If I use the = sign on a theta join is it exactly the same as just …

Read More
What are projection and selection?

What is the difference between projection and selection? Is it: Projection --> for selecting the columns of table; and Selection ---> to select the rows of table? So are projection and selection vertical and horizontal slicing respectively?

Read More
What is the difference between Select and Project Operations

I'm referring to the basic relational algebra operators here. As I see it, everything that can be done with project can be done with select. I don't know if there is a difference or a certain nuance that I've missed.

Read More