CODEWITHSUNDEEP
mysqlsqlsubquerycorrelated-subquery
Jason Woo
Jason Woo

Reputation: 321

SQL correlated subquery of the same table

I have tables:

Person(SSN, name, address)
Car(license, year, model)
Accident(license, accident-date, driver, damage-amount)
Owns(SSN, license)

The question asks:

Find the license number of all cars that have been involved in more than one accident (DO NOT RETURN DUPLICATES).

One of the answers is:

SELECT DISTINCT A1.license 
FROM Accident A1 
WHERE A1.license IN (SELECT A2.license 
                     FROM Accident A2 
                     WHERE A1.accident-date <> A2.accident-date)

It never checks that A1.license = A2.license in the correlated subquery. So to my understanding, the subquery checks if a tuple in A1 has a different accident date than a tuple in A2, even if the two tuple have different licenses. So shouldn't this be wrong?

EDIT: It is assumed that a car cannot have more than one accident in a day.

Upvotes: 0

Views: 909

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Actually IN operator is checking for licenses equality. Lets make a test. Say Accidents table contains rows:

License  AccidentDate
L1       20151001
L1       20151020
L2       20151025

Correlated subquery is evaluated for each row of the outer query. Imagine a cursor that loops over the rows.

For row1 A.Licence is L1. Inner subquery just checks for dates different from 20151001 and it will find row2 and row3(L1, L3). So A.Licence i.e. L1 is in the resultset of inner subquery and it will be returned in final result. So do second row. But row3 will not be included, because there are no L2 in dates other then 20151025. So the query is correct.

Upvotes: 1

Related Questions