Why my query does not return correct data (NORTHWIND)? select * 
from orders o
WHERE EXISTS 
(
  select 1 
  from orders o2 
  where o2.EmployeeID = o.EmployeeID  
    AND YEAR(o2.OrderDate) = 1997 
    and MONTH(o2.OrderDate) = 9 
    AND DAY(o2.OrderDate) = 5
)


I expect to receive only two records in result instead of 228. 
As you can see this is EXISTS on the same table. 
Thank you in advance.
There are two orders with an orderdate=1997-09-05.  One for employee=4 and one for employee=7.  Now, for employee=4, there are 156 rows on the order table and for employee=7, there are 72 rows on the order table.  That totals to 228.  Your query is returning all orders where the employeeid matches those two id's.

Alter your query to include a check for o2.OrderDate=o.OrderDate

SELECT *
FROM orders o
WHERE EXISTS (
        SELECT 1
        FROM orders o2
        WHERE o2.EmployeeID = o.EmployeeID
            AND YEAR(o2.OrderDate) = 1997
            AND MONTH(o2.OrderDate) = 9
            AND DAY(o2.OrderDate) = 5
            AND o2.OrderDate = o.OrderDate
        )




Based on your example, you would get the same results by simplifying the query to:

select * from orders where orderdate = '1997-09-05'