Location of the where keyword in SQL connection Query

Because I am clumsy and not rigorous in thinking, I am really not good at writing SQL statements. Please skip this article.

The background is not described much. first create a table and insert test data. All fields have comments.
Copy codeThe Code is as follows:
-- Doctor's table
Create table doctor
(
Id int identity (1, 1), -- ID auto-Increment
DocNumber NVARCHAR (50) not null, -- Doctor code
Name nvarchar (50) not null -- doctor's NAME
)
Go

-- Insert Test Data
Insert into doctor
VALUES ('007 ', 'Tom ')
Insert into doctor
VALUES ('008 ', 'john ')
Insert into doctor
VALUES ('009', 'Jim ')

-- Number source table (registration table)
Create table Nosource
(
Id int identity (1, 1 ),
DocNumber NVARCHAR (50) not null, -- corresponds to the doctor code in the doctor's table
WorkTime DATETIME NOT NULL
)

Go
-- Insert Test Data
Insert into Nosource
VALUES ('007 ', '123 ')
Insert into Nosource
VALUES ('007 ', '123 ')
Insert into Nosource
VALUES ('007 ', '123 ')
Insert into Nosource
VALUES ('008 ', '123 ')

After the table is created, the test data is OK. The following describes the requirements.

1. Check the relevant information of each doctor and the number of sources owned by the doctor.

This is simply too simple. Even friends who have just learned helloWorld and a little bit of database basics will be very sincere about BS. But the code is still written.
Copy codeThe Code is as follows:
-- Simple grouping query.
Select count (nos. id) AS PersonNumSounceCOUNT, -- total
Dct. id as docID,
Dct. NAME,
Dct.doc Number,
Nos. workTime
FROM doctor AS dct
Left join Nosource AS nos ON dct.doc Number = nos.doc Number
Group by dct. ID,
Dct. NAME,
Dct.doc Number,
Nos. workTime

It's really easy. A small group can solve the problem. What kind of Customs do you want to sell.

Now, the requirement changes and needs to be matched according to the condition: the workTime of the source table must be greater than the current date, otherwise it will not be matched.
If the workTime condition does not match the doctor, the value of the corresponding PersonNumSounceCOUNT field should be 0. For example, if Dr Jim does not match the matching source, the value of the PersonNumSounceCOUNT field should be 0. Look up to 40 degrees in the sky. Can you use the where keyword to filter the results and then query them all at once? Try it.
Copy codeThe Code is as follows:
Select count (nos. id) AS PersonNumSounceCOUNT, -- total
Dct. ID,
Dct. NAME,
Dct.doc Number,
Nos. workTime
FROM doctor AS dct
Left join Nosource AS nos ON dct.doc Number = nos.doc Number
Where datediff (day, GETDATE (), nos. workTime)> 0
Group by dct. ID,
Dct. NAME,
Dct.doc Number,
Nos. workTime

I believe someone will write the above Code. However, after the query is executed, it is found that it completely does not meet the requirements. Even the basic information and table records of Dr. Jim were filtered out and disappeared. What's going on?

The reason is simple. When the "where" keyword is used after the connection query, the data in the result set of the connection query is filtered. The data in the left table (Doctor table) is filtered out because the conditions in the right table (No. source table) do not match.

Therefore, the above phenomenon will occur (Dr Jim's information and records are missing ). Is it possible to check it all at once? How should we implement it?

In fact, the correct method should be as follows:
Copy codeThe Code is as follows:
Select count (nos. id) AS PersonNumSounceCOUNT, -- total
Dct. ID,
Dct. NAME,
Dct.doc Number,
Nos. workTime
FROM doctor AS dct
Left join (SELECT *
FROM Nosource
Where datediff (day, GETDATE (), workTime)> 0
) AS nos ON dct.doc Number = nos.doc Number
Group by dct. ID,
Dct. NAME,
Dct.doc Number,
Nos. workTime

Run the following command again. OK is the result that meets the requirements. The idea is: you only need to filter the right table, and then use the result set filtered by the subquery as the right table for connection query, and then connect to the group ......

In fact, writing simple and high-performance SQL statements requires strong logic thinking capabilities (which is inseparable from mathematics) and experience. There is also a simpler way of writing:
Copy codeThe Code is as follows:
SELECT sum (case when nos. workTime> getdate then 1 else 0 end) AS PersonNumSounceCOUNT, -- total
Dct. id as docID,
Dct. NAME,
Dct.doc Number
FROM doctor AS dct
Left join Nosource AS nos ON dct.doc Number = nos.doc Number
Group by dct. ID,
Dct. NAME,
Dct.doc Number

In this way, I don't know if you can understand it. It means this in general. The author's ability and level of expression are indeed limited, and it is inevitable that there will be deviations. Please forgive me!