CODEWITHSUNDEEP
sql
Lieberta
Lieberta

Reputation: 147

SQL Statement: How does Exist return its value?

Select * from FMN_XX.order odr where 
exists(
    select (1) from FMN_XX.order_expired exp 
    where odr.order_id = exp.order_id
);

Above is the example query for exists. I have tried looking around and reading about it but I just can't get my head wrapped around it.

When I query individually the query inside the EXISTS bracket, it returns 1 as expected and no order_id from order_expired since I didn't query for column there.

But when I run the whole query, it returns the correct number of rows! My question is, how does it know the order_ID from order_expired table when I don't even query for order_id from the order_expired table? How does it compare to get the right rows?

Extra note: Currently, in the order table, I have 19779 rows and in order_expired table, I have 8506 rows. The final result I get when I added count at the outer query layer is 8506 rows, meaning, somewhat the EXISTS statement has filters the rows. If it should just returns if at least one order_id is hit... shouldn't the whole query returns the whole 19779 rows?

Upvotes: 1

Views: 2056

Answers (3)

Insac
Insac

Reputation: 810

You said:

When I query individually the query inside the EXISTS bracket, it returns 1 as expected and no order_id from order_expired since I didn't query for column there.

However, I guess that you haven't really used the EXISTS query as it would have been:

select (1) from FMN_XX.order_expired exp 
 where odr.order_id = exp.order_id

and it would return error because it doesn't know what odr is.

The clause where odr.order_id = exp.order_id is exactly what gives the correlation between the main query and the EXISTS subquery.

So, the query would be roughly translated in natural language as:

select all the orders that exist into the expired orders table by looking it up by the order_id field

Upvotes: 0

Emil Holub
Emil Holub

Reputation: 168

Exists is similar to join - you delimit your output based on values in another table (or even the same table with different condition.).

The difference in useablity is that the exists function does not care for duplicit values, it checks only if there are query results existing with your condition.

In other words, if your table order_expired would be unique in column order_id, then you should get the same result from your query as from this query:

Select odr.* from FMN_XX.order odr 
join FMN_XX.order_expired exp on odr.order_id = exp.order_id;

However if it is not unique then the join would delimit your results, but at the same time duplicate orders from order_expired.

One more difference is also, that with eixsts you cant use any values from the table inside the exists subquery - with join you can use any columns from joined tables.

Upvotes: 1

Andrei Amarfii
Andrei Amarfii

Reputation: 725

how does it know the order_ID from order_expired table when I don't even query for order_id from the order_expired table? How does it compare to get the right rows?

The condition from WHERE clause of the exists's SUBSELECT gives this information :

the odr.order_id is the column from main SELECT, whereas the exp.order_id is the column from exists SUBSELECT

 where odr.order_id = exp.order_id

if the condition above returns TRUE then the record will appear in the result set.

https://en.wikipedia.org/wiki/Correlated_subquery

Upvotes: 1

Related Questions