A
SQL technique which vaguely approaches
deep magic by, instead of
joining to another
table as is usual,
joins to another
instance of
itself.
It is typically used for
obscure or particularly difficult requirements such as analyzing
database integrity. It can also provide a means of implementation of certain requirements which avoids the inefficiencies of resorting to a
cursor.
As an example, here is a SQL
query which hypothetically returns the most recent login data for each user of an application:
select * from LoginLog
where LoginLogID not in
(select t1.LoginLogID from LoginLog t1, LoginLog t2
where t1.UserID = t2.UserID
and t1.LoginTime > t2.LoginTime)