How to represent "not exists" in relational algebra?

Question

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

Claudclauddetta

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. – Broccoli

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 minimal reproducible example 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 How to Ask, other help center links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. PS Replacing WHERE EXISTS by JOIN in SQL is a faq. – Broccoli

Does this answer your question? Relational algebra for banking scenario – Broccoli

Answer 1

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).

Homopterous

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. – Broccoli

Answer 2

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.

Poussette

if there is query like select * from S where exists(select * from Y); how can i write that in relational algebra – Claudclauddetta

@sudh: The comment section can't accurately display my response, so see the addendum to my answer. – Poussette

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. – Matchlock

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). – Waterscape

It is relational calculus not relational algebra. – Lumbar

Answer 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.

Waterscape