>, <, <>, = Operators for internal join conditions

Question

My boss threw this code in my direction, and it's hard for me to understand how the last ON statement works. I do not think that he fully understands this (but he is doing his job). It's actually just interesting to learn more about how SQL works. Thank you very much!

Here is the On instruction

 and (A.Submitted_Date > X.Submitted_Date))) 

And here is the request

  SELECT AA.ID, AA.Submitted_Date as Date_Status FROM Report as AA where AA.Submitted_Date in --START ( SELECT X.Submitted_Date FROM Report as A inner join --Start Find All Dates Submitted ( SELECT [ID],[Submitted_Date] FROM Report where not(Submitted_Date is null and Cleared_Date is null) group by ID, Submitted_Date) as X --End Find all Dates Submittd --below is the conditions of the join ON A.ID = X.ID and A.ID= AA.ID --THIS IS THE CONDITION I AM CONFUSED ABOUT!!!! and (A.Submitted_Date > X.Submitted_Date))) group by X.Submitted_Date) and not AA.Submitted_Date is null group by AA.ID, AA.Submitted_Date 

Here is an example of dates from table A

 2012-11-27 00:00:00.000 2012-11-27 00:00:00.000 2012-11-27 00:00:00.000 2012-12-10 00:00:00.000 2012-11-27 00:00:00.000 2012-11-27 00:00:00.000 2012-11-29 00:00:00.000 2012-12-05 00:00:00.000 2012-12-12 00:00:00.000 

Here is an example of dates from table X

 2012-11-27 00:00:00.000 2012-11-29 00:00:00.000 2012-12-05 00:00:00.000 2012-12-10 00:00:00.000 2012-12-12 00:00:00.000 

Here is the result before the last state

 2012-11-27 00:00:00.000 2012-11-29 00:00:00.000 2012-12-05 00:00:00.000 2012-12-10 00:00:00.000 2012-12-12 00:00:00.000 

Here is the result with A.Sub> X.Sub

 2012-11-27 00:00:00.000 2012-11-29 00:00:00.000 2012-12-05 00:00:00.000 2012-12-10 00:00:00.000 

I am confused why these dates appear. What compares between A and X? Will the values ​​in invariable like X always be, therefore, do not lead to final data? Thanks for the help!

Answer 1

A join condition does two things. Firstly, this ensures that x.Submitted_Date not null. This directly contradicts the statement whre directly above it. In this expression, Submitted_Date may be NULL if Cleared_Date not null.

A non-null check is just a use. A join is an inner join, so only rows in x and a that match are stored. When x.Submitted_Date is null , the comparison A.Submitted_Date > X.Submitted_Date evaluates to FALSE (actually NULL, but NULL is equivalent to FALSE in this context).

Secondly, this ensures that during Report there is another record for this id . This happens through the same comparison: A.Submitted_Date > X.Submitted_Date says that a has a later record than x . The condition is true for all Submitted_Date values, except for the maximum value.

So, in short, the conditions take all the provided date values, except for the last date.

The condition may be equivalent to:

 where AA.Submitted_Date <> (select MAX(x.Submitted_Date) from Report where x.id = AA.id) 

I say because Cleared_Date makes this doubtful. But, I think they are the same. The where clause in the original query is executed when x.Submitted_Date not NULL. A cleared date may contain additional lines, but they must have a zero date. Thus, he pulls out everything except the most filed date.

I would suggest that the logic was even more complex, and this is a “simplified” version that can be simplified further.

Answer 2

It would be better if your data included an ID field. And I suppose the identifier is not unique. In addition, I suspect that your sample choice is not ideal for understanding the situation.

Given these limitations on your question, I think that confuses you, in fact, this condition is A.ID = X.ID. This makes you think that the dates will be the same, and therefore there should be no data.

If you have a report table with:

 1 2012-11-27 1 2012-11-29 

then cross join creates:

 1 2012-11-27 1 2012-11-27 1 2012-11-27 1 2012-11-29 1 2012-11-29 1 2012-11-27 1 2012-11-29 1 2012-11-29 

Which passes the criteria A.ID = X.ID, and then A.date> X.date will return

 1 2012-11-29 1 2012-11-27 

edit: removed "Record 2012-11-27 should not be part of the sample-based result." because the date field is taken from X, not from A.