Explanation of the location of where keyword in SQL connection query

Time:2022-5-27

Because the author is naturally clumsy, and his thinking is not rigorous, and he is really not good at writing SQL statements, experts should not laugh, please skip this article directly.

The background will not be introduced. First create a table and insert test data. Those fields have comments

Copy codeThe code is as follows:
–Doctor table
CREATE TABLE doctor
    (
Id int identity (1, 1), — ID self growth
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 table
      workTime DATETIME NOT NULL
    )

go
–Insert test data
INSERT  INTO Nosource
VALUES  ( ‘007’, ‘20120819’ )
INSERT  INTO Nosource
VALUES  ( ‘007’, ‘20120820’ )
INSERT  INTO Nosource
VALUES  ( ‘007’, ‘20120821’ )
INSERT  INTO Nosource
VALUES  ( ‘008’, ‘20120821’ )

After the table is built, the test data is OK. Let’s start talking about demand.

1. Find out the relevant information of each doctor and the number of signal sources owned by the doctor.

This is simply too simple. Maybe even friends who have just learned HelloWorld and a little database foundation will be seriously injured. But the code is still written.

Copy codeThe code is as follows:
–Simple grouping query can be done
Select count (nos.id) as personnumsoundcount, — total
        dct.ID AS docID ,
        dct.NAME ,
        dct.docNumber ,
        nos.workTime
FROM    doctor AS dct
        LEFT JOIN Nosource AS nos ON dct.docNumber = nos.docNumber
GROUP BY dct.ID ,
        dct.NAME ,
        dct.docNumber ,
        nos.workTime

It’s really simple. A small group can handle it. What else to sell.

Now the demand changes, and it needs to be matched according to the conditions: it is valid only if the worktime of the source table is 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 personnumsoundcount field should be 0; For example: Dr. Jim has no matching and qualified number source, and the value of his personnumsoundcount field should be 0. Look up at the sky 40 degrees and think about whether you can filter with the where keyword and query it at one time? Try it.

Copy codeThe code is as follows:
Select count (nos.id) as personnumsoundcount, — total
        dct.ID ,
        dct.NAME ,
        dct.docNumber ,
        nos.workTime
FROM    doctor AS dct
        LEFT JOIN Nosource AS nos ON dct.docNumber = nos.docNumber
WHERE   DATEDIFF(day, GETDATE(), nos.workTime) > 0
GROUP BY dct.ID ,
        dct.NAME ,
        dct.docNumber ,
        nos.workTime

I believe someone will write the above code. However, after executing the query, it is found that it does not meet the requirements at all. Even Dr. Jim’s basic information and table records were filtered out and disappeared. What’s going on?

The reason is simple. Using the “where” keyword after the connection query will filter the data in the result set of the connection query. Because the conditions of the right table (number source table) do not match, the data of the left table (Doctor table) will also be filtered out.

Therefore, the above phenomenon will occur (Dr. Jim’s information and records are missing). Is it possible to find out at one time? How to realize it?

In fact, the correct way of writing should be as follows:

Copy codeThe code is as follows:
Select count (nos.id) as personnumsoundcount, — total
        dct.ID ,
        dct.NAME ,
        dct.docNumber ,
        nos.workTime
FROM    doctor AS dct
        LEFT JOIN ( SELECT  *
                    FROM    Nosource
                    WHERE   DATEDIFF(day, GETDATE(), workTime) > 0
                  ) AS nos ON dct.docNumber = nos.docNumber
GROUP BY dct.ID ,
        dct.NAME ,
        dct.docNumber ,
        nos.workTime

Execute it again. Sure enough, it is the result of meeting the requirements. The idea is: just filter the right table, take the filtered result set (using sub query) as the right table of the connection query, and then connect and group

In fact, writing concise and high-performance SQL statements requires strong logical thinking ability (inseparable from Mathematics) and experience. There is a simpler way to write:

Copy codeThe code is as follows:
Select – sum (case when nos.worktime > getdate then 1 else 0 end) as personnumsoundcount, — total
dct.ID AS docID ,
dct.NAME ,
dct.docNumber
FROM    doctor AS dct
LEFT JOIN Nosource AS nos ON dct.docNumber = nos.docNumber
GROUP BY dct.ID ,
dct.NAME ,
dct.docNumber

To explain this way, I don’t know whether you can understand it. Anyway, the general meaning is like this. The author’s expression ability and level are indeed limited, and there are inevitable deviations. I hope readers will understand!

Recommended Today

A front-end developer's Vim is the same as an IDE

Here is my new configurationjaywcjlove/vim-webI've been grinding it, and it's basically ready to use. Take it out and cheat the star Install The latest version of Vim 7.4+ uses (brew install macvim) installation, vim version updatebrew install macvim –override-system-vim View configuration locations # Enter vim and enter the following characters :echo $MYVIMRC download vim-web Download […]