Transact-SQL

query uses EXISTS

SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');

query uses IN

SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');

Here is the result set.

FirstName                                          LastName
-------------------------------------------------- ----------
Barry                                              Johnson
David                                              Johnson
Willis                                             Johnson
(3 row(s) affected)

select i.userid,i.action,i.createdOn,count(*)
from
(

select
a.action,
l.userid,
RTRIM(char(MONTH(createdOn) )) || RTRIM(char(YEAR(createdOn))) as createdon
from IBSADMIN.ActionAuditTrail a,UserLoginAuditTrail l
where a.action ='auditAction.registration'
and a.LOGINTRAILID = l.LOGINTRAILID
and month(a.createdOn) = 3
and EXISTS
   (
   select 1
   from IBSADMIN.ActionAuditTrail aa,UserLoginAuditTrail ll
   where aa.action ='auditAction.login'
   and month(aa.createdOn) = 3
   and l.userid = ll.userid
   )

)
i group by  i.userid,i.action,i.createdOn

ref=technet.microsoft.com