mercredi 27 janvier 2016

Difficulty to understand SQLite exists and its subquery

Assume there is one table called Temp, its schema and data are listed blow

Table Temp :

A         B       
----      ----

1         1        # Row 1 
1         5        # Row 2 
2         2        # Row 3  

SQLite Query :

1) DELETE FROM Temp WHERE EXISTS (SELECT 1 FROM Temp AS IL2 WHERE Temp.A = IL2.A AND Temp.B = 1 AND IL2.B = 5)
2) DELETE FROM Temp WHERE EXISTS (SELECT 1 FROM Temp AS IL2 WHERE Temp.A = IL2.A AND Temp.B = 5 AND IL2.B = 1)

Question 1) What is the meaning of "Temp.A = IL2.A"? IL2 is alias of Temp, it compares to itself? I get lost here.

Question 2) Query 1 and 2 deletes Row 1 and 2 by experiment, respectively. It seems to me that the return value of the sub-query "(SELECT 1 FROM Temp AS IL2 WHERE Temp.A = IL2.A AND Temp.B = 5 AND IL2.B = 1)" is controlled by the value of "IL2.B". I thought this sub-query return both Row 2 and Row 3.

Aucun commentaire:

Enregistrer un commentaire