5. SQL quot;01"-query & SQL course comparison & quo

-- taking this example as an example, we first introduce several commonly used table joins. In this example, only left join is used. Others can try-test:

Join within inner join, the result is that both tables contain rows of Sid

-- left join is connected to the left. The result is the Sid in the left table, and the null value null is not found in the right table.

-- right join join to the right. The result is based on the Sid in the right table, and the null value null is not found in the left table.

-- full outer join full external connection, the result is containing two tables of data

Example: inquire about the information and course scores of students with higher scores in "01" than in "02".

-- 1. The first method: connect to the left, query the student information of both '01' and' 02' courses, and then compare, isnull usage, return score if score has a value, and return 0 if score is null

Select a. Score for course '01', c.score [score for course' 02'] from Student a

Left join SC b on a.Sid=b.Sid and b.Ciddance 01'

Left join SC c on a.Sid=c.Sid and c. Ciddance 02'

Where b.score > isnull (c.scorePhone0)

2. The second way of writing

Select a.*, b.score [score for course '01'], c.score [score for course' 02'] from Student a, SC b, SC c

Where a.Sid = b.Sid and a.Sid = c.Sid and b.Cid = '01' and c.Cid =' 02' and b.score > c.score