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)

Log in or register to write something here or to contact authors.