SQL subqueries with 'exists' clause and multiple tables-CodePudding

sorry, not enough reputation to post images. I have 3 SQL tables:

x1: {'a':1,2,3,4,5},

x2: {'c':1,1,1,2,2,3,3, 'd':1,3,5,1,3,1,1},

x3: {'b':1,3,5}

The query is:

select a from x1 
where not exists (
  select * from x3
  where not exists (
    select *
    from x2
    where x1.a = x2.c and x3.b=x2.d
  )
)

The result from the following query is '1', but I can't understand what are the steps taken to get to that result.

What is being returned in which subquery?

CodePudding user response：

In a sentence, your query is looking for values of a in x1 that exist as a left member of the couples (c, d) in x2 for every single value of b from x3 as right member. In this case when you take a = 1, you have all three couples (1, 1), (1, 3) and (1, 5) in x2.

Step by step: outer query goes through each a from x1. For each a, inner subquery (inside first where not exists) goes through each b from x3. Innermost subquery compares the selected couple (a, b) against all couples (c, d) in x2. If there is no occurence of this (a, b) in x2, then a is not returned. Basically: give me each a for which there doesn't exist a b, for which (a, b) is not in x3 <-> give me each a where (a, b) exists in x2 for every single b from x3.

CodePudding user response：

I will try to explain. Your query will fetch records from table a for which the result set of

is empty(ie is asserted by not exists)

Consider the data in the tables

The values in a are 1,2,3,45

Lets check for a=1

We got against a=1 with c=1 three records in Table x2

And for the 3 records all of the column d values are present in table x3. This means the output of the block will return empty for a=1 and therefore will be present in the final output.

Check for a=2, the possible values in d are 1 and 3 and as we got 5 in table x3 the query will return a non-empty result

Similarly for a=3 the inner query returns 3 and 5 from table x3

For a=45, as a doesnt exist in table x2,all of the records table x3 gets returned -> 1,3,5

Therefore the only a which satisfy the empty result set is a=1 which is the answer.