5

How do I represent the SQL "not exists" clause in relational algebra?

philipxy
  • 14,867
  • 6
  • 39
  • 83
sudh
  • 1,085
  • 4
  • 12
  • 13
  • What does "represent" mean? A NOT EXISTS expression (which is not a clause) is a boolean expression but RA expressions are relation-valued. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. – philipxy Feb 27 '20 at 22:35
  • There are many RAs (relational algebras). They differ in operators & even what a relation is. Give definitions & a reference for yours. Eg textbook name, edition & page. Nested RA calls form a programming language. So give as much of a [mre] as you can, even if you are not actually running code. But--Google 'run relational algebra online'. Please show what parts you are able to do. See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. PS Replacing WHERE EXISTS by JOIN in SQL is a faq. – philipxy Jan 27 '21 at 19:26
  • Does this answer your question? [Relational algebra for banking scenario](https://stackoverflow.com/questions/24423150/relational-algebra-for-banking-scenario) – philipxy Sep 25 '21 at 18:43

3 Answers3

3

The SQL NOT EXISTS construct can be represented in relational algebra by the antijoin ▹.

The antijoin L ▹ R of two relations L and R selects those tuples of L that do not join with any tuple in R. It can be defined in terms of set difference and left semijoin as follows:

L ▹ R = L - (L ⋉ R).
user2262867
  • 41
  • 6
  • This does not explain what your expression has to do with SQL expressions or in what sense you are "representing NOT EXISTS". The question itself is poor because it doesn't explain "represents". In particular a NOT EXISTS expression (which is not a clause) is a boolean expression but RA expressions are relation-valued. – philipxy Jan 27 '20 at 00:01
-1

I think you're looking for the existential quantifier (∃), which you could then negate (~∃).

Response to comment: I don't remember most of my relational algebra, but if I was going to take a stab at it I would guess something along the lines of: σ∃σ(Y)(S). Or possibly π∃π(Y)(S); I don't quite remember if you'd want selection or projection for that.

eldarerathis
  • 35,455
  • 10
  • 90
  • 93
  • if there is query like select * from S where exists(select * from Y); how can i write that in relational algebra – sudh Sep 17 '10 at 20:22
  • @sudh: The comment section can't accurately display my response, so see the addendum to my answer. – eldarerathis Sep 17 '10 at 20:47
  • 1
    I'm 99% sure the existential quantifier is not part of relational algebra....It doesn't show up anywhere on the RA wikipedia page, also I have not seen it in my textbook, or in lectures. – Ethan Feb 26 '13 at 23:48
  • 2
    The existential quantifier, is part of relational algebra. It is in my textbook: Databases Systems by Ramez Elmasri (Global Edition, 6th edition, p.173). I think it is easier however to rewrite your query to get rid of the EXISTS operation altogether. (See my answer for that). – Ewoud Mar 07 '13 at 12:03
  • 2
    It is relational calculus not relational algebra. – Martin Smith Oct 11 '14 at 21:55
-3

In my case I solved this issue by rewriting the query, 

SELECT *
FROM contactperson
WHERE EXISTS(
   SELECT *
   FROM person
   WHERE contactperson.personId = person.id)

to:

SELECT *
FROM contactperson
WHERE personId = (
   SELECT id
   FROM person
   WHERE contactperson.personId = person.id)

It returns the same result and is easier to rewrite to relational algebra using a join.

Ewoud
  • 13,845
  • 5
  • 18
  • 23