Blufftl Blufftl - 1 year ago 44
SQL Question

JOIN Subselect range in ON Condition? (Why and behavioral benefits?)

The statement in question is working correctly, I just need to understand why and how!

I have encountered the following Syntax in a statement which gets information for employee analysis.

Never seen anything like it and cannot find anything about the behavior using google. Hope you people can help me understand how this works and how to use it.

Table 1 Employees (EmployeeID, Name, Birthdate, ...)

Table 2 Contracts (ContractID, EmployeeID, Startdate, Enddate, ...)

Table 3 Time-models (TimeID, ContractID, EmployeeID, MonthlyDate, ...)

Table 4 Insurance (InsuranceID, ContractID, EmployeeID, JobType, ...)

The subselect in the code later on outputs:

Columname MonthlyDate
Values('2016-02-01 00:00:00.000',
'2016-04-01 00:00:00.000',
'2016-01-01 00:00:00.000',
'2016-03-01 00:00:00.000',
'2016-06-01 00:00:00.000',
'2016-05-01 00:00:00.000',
'2016-07-01 00:00:00.000')

The SQL in question looks like this:

SELECT E.EmployeeID+C.ContractID, D.Date, E.Name

FROM Employees AS E

LEFT OUTER JOIN Contract AS C ON E.EmployeeID = C.Employee ID

--This is where the SQL I was talking about starts
SELECT MonthlyDate
FROM Time-models
WHERE MonthlyDate >= Convert(DATETIME, '2016-01-01', 102)) AS D
ON (D.MonthlyDate >= C.ContractStartDate AND D.MonthlyDate <= C.ContractEndDate)
/*I can not explain this On-Condition*/

LEFT OUTER JOIN Insurance as I
ON (I.EmployeeID = E.EmployeeID AND I.ContractID = C.ContractID)

WHERE I.JobType = 'Clerk'
AND (I.InsuranceStartDate <= D.MonthlyDate AND I.InsuranceEndDate >= D.MonthlyDate)
/* I can not understand which compare values are used in D.MonthlyDate
because of the the vague ON-Condition */

Answer Source

The WHERE condition of your query is turning the LEFT JOIN into an INNER JOIN. (If a row doesn't match then the I columns will be NULL and fail the WHERE conditions.)

In addition:

  • You do not need the subquery at all.
  • SQL Server recognizes dates in the format YYYYMMDD and almost always in the format YYYY-MM-DD without conversion.

So, you might as well re-write the query as:

SELECT E.EmployeeID+C.ContractID, D.Date, E.Name
FROM Employees E JOIN
     Contract C
     ON E.EmployeeID = C.Employee ID JOI
     [Time-models] d
     ON D.MonthlyDate >= C.ContractStartDate AND
        D.MonthlyDate <= C.ContractEndDate AND
        MonthlyDate >= '2016-01-01' JOIN
     Insurance as I 
     ON I.EmployeeID = E.EmployeeID AND
        I.ContractID = C.ContractID
WHERE I.JobType = 'Clerk' AND
      I.InsuranceStartDate <= D.MonthlyDate AND
      I.InsuranceEndDate >= D.MonthlyDate;

I'm not sure if this answers your question, though.