Geek Answers Handbook

Representation of a subquery in relational algebra

How to represent a subquery in relationship algebra? Am I putting a new select in the previous selection condition?

SELECT number FROM collection WHERE number = (SELECT anotherNumber FROM anotherStack);
+6
sql relational-algebra
source share
2 answers

You just rewrite this as join .

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

  • Take a projection of anotherNumber from anotherStack
  • Rename anotherNumber from the result of step 1 as number
  • Natural Attach the result of step 2 to the collection
  • Take the final projection number from the result of step 3
+6
source share

The answer depends on which operators your algebra belongs to. The half-join operator will be most useful here.

If the common attribute was named number in both respects, it will be a semi-join followed by the projection number . Suppose a sem-join MATCHING named MATCHING , according to Tutorial D :

 ( collection MATCHING anotherStack ) { number }

As indicated, the attribute must first be renamed:

 ( collection MATCHING ( anotherStack RENAME { anotherNumber AS number } ) { number }

If standard SQL (SQL-92) JOIN can be considered, loosely speaking, a relational operator, then it is true that SQL does not have a semi-join. However, it has several comparison predicates that can be used to write a semi-join operator, for example. MATCH :

 SELECT number FROM collection WHERE MATCH ( SELECT * FROM collection WHERE collection.number = anotherNumber.anotherStack );

However, MATCH not widely supported in real SQL products, so why IN (subquery) usually written using IN (subquery) or EXISTS (subquery) (and I suspect why you called the "subquery" in your question, that is, the term half-join is not good known among SQL practitioners).

Another approach would be to use the intersection operator, if available.

Something like (pseudo code):

 ( collection project number ) intersect ( ( anotherStack rename anotherNumber as number ) project number )

In SQL:

 SELECT number FROM collection INTERSECT SELECT anotherNumber FROM anotherStack;

This is pretty well supported in real life (SQL Server, Oracle, PostgreSQL, etc., but not MySQL).

+1
source share

More articles:


All Articles