CODEWITHSUNDEEP
sqlt-sqlcorrelated-subquery
hmyroom
hmyroom

Reputation: 11

Correlated subquery SQL

In the following picture showing two tables and a SQL query.

The question of this assignment: what will be the result of the query?

I know the answer, but I don't really understand how the SQL query works, especially in the row, which I marked with question mark.

So far, I realized that first we join two tables from subquery

SQL query:

select Count(*)  
from OrderDetails as OD 
inner join [Order] as O on OD.idOrder = O.id  
where SalePrice = all (select Price  
                       from OrderDetails 
                       inner join [Order] on OrderDetails.idOrder = [Order].id  
                       where idCustomer != O.idCustomer 
                         and OrderDetails.idProduct = OD.idProduct)

Upvotes: 1

Views: 225

Answers (1)

Martin Smith
Martin Smith

Reputation: 453940

As a logical description first start by expanding out the join as this is used both by the outer query and the sub query.

enter image description here

The first row in the image above has idCustomer of 1 and idProduct of 1. The subquery is looking for all rows with the same product id and different customerid. For the first row there are two rows matching this combination (highlighted yellow in both columns). These are the ones with the Price column highlighted red below.

enter image description here

The SalePrice column from the outer row has a value of 1. This is the same as all of the red highlighted Price values (i.e. none of them have a different value) so the first outer row meets the = ALL predicate and contributes to the count.

The same process is repeated for the remaining 9 rows returned by the join.

Upvotes: 2

Related Questions