CODEWITHSUNDEEP
mysqlsql
johnson
johnson

Reputation: 283

SQL query with EXISTS not working as I thought

Hi these two SQL Queries return the same result

SELECT DISTINCT ItemID
FROM Sale INNER JOIN Department
ON Department.DepartmentID = Sale.DepartmentID
WHERE DepartmentFloor = 2
ORDER BY ItemID


SELECT DISTINCT ItemID
FROM Sale
WHERE EXISTS
(SELECT *
FROM Department
WHERE Sale.DepartmentID = Department.DepartmentID
AND DepartmentFloor = 2)
ORDER BY ItemID;

The Subquery Inside the Exists returns True So why doesnt the secod query return the equivalent of

SELECT DISTINCT ItemID
FROM Sale

Which guves a different result from the two above.

Upvotes: 1

Views: 1822

Answers (3)

Shawn
Shawn

Reputation: 4786

The subquery isn't always returning true. It will evaluate for each row, joining on DepartmentID where the DepartmentFloor is 2.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Sale ( ItemID int, DepartmentID int ) ;

INSERT INTO Sale ( ItemID, DepartmentID )
VALUES (1,1), (2,2), (3,3), (4,1), (5,4), (6,2), (7,3), (8,4) ;

CREATE TABLE Department ( DepartmentID int, DepartmentFloor int ) ;

INSERT INTO Department ( DepartmentID, DepartmentFloor )
VALUES (1,1), (2,1), (3,2), (4,2) ;

Query 1:

SELECT *
FROM Department
WHERE DepartmentFloor = 2

Results: This lists only the Departments on DepartmentFloor 2.

| DepartmentID | DepartmentFloor |
|--------------|-----------------|
|            3 |               2 |
|            4 |               2 |

Query 2:

SELECT *
FROM Sale

Results: This lists ALL of your Sales.

| ItemID | DepartmentID |
|--------|--------------|
|      1 |            1 |
|      2 |            2 |
|      3 |            3 |
|      4 |            1 |
|      5 |            4 |
|      6 |            2 |
|      7 |            3 |
|      8 |            4 |

Query 3:

SELECT *
FROM Sale
WHERE DepartmentID IN (3,4)

Results: And this one shows what is the equivalent of you EXISTS statement. It only shows 4 rows that will match up in my data. So you'd only get back ItemIDs 3,5,7 and 8.

| ItemID | DepartmentID |
|--------|--------------|
|      3 |            3 |
|      5 |            4 |
|      7 |            3 |
|      8 |            4 |

Upvotes: 1

Aaron Dietz
Aaron Dietz

Reputation: 10277

You are getting confused by EXISTS().. It occurs on a line by line basis, based on table correlation, not just a single true/false. This line of your subquery is your correlation clause:

Sale.DepartmentID = Department.DepartmentID

It is saying "Only show the Sale.ItemIDs where that ItemID's Sale.DepartmentID is in Department."

It achieves the same function as a join predicate, like in your first query:

FROM Sale S
JOIN Department D on S.DepartmentID = D.DepartmentID --here

Conversely, this query:

SELECT DISTINCT ItemID
FROM Sale

Has no limiting factor.

As an aside, you also further limit the results of each query with:

WHERE DepartmentFloor = 2

But I don't think that is the part that is throwing you off, I think it is the concept that a correlated subquery occurs for each record. If you were to remove your correlating clause, then the subquery would actually return true always, and you would get all results back.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

because the uppper part of the query is equivalent to

   SELECT DISTINCT ItemID FROM Sale where EXISTS (true)

the upper is the only query that really check the condition ..

Upvotes: 0

Related Questions