a********e
发帖数: 198 | 1 I run the simple query in MS sql2012
SELECT count(*) from PNTD.dbo.PEOPLE p
LEFT JOIN PNTD.dbo.AUTHORS at ON at.PEOPLEID = p.PEOPLEID ;
SELECT count(*) from PNTD.dbo.PEOPLE p
the return are 586 and 560.
why the left join return more rows? should the first query return less rows? |
B*****g
发帖数: 34098 | 2 你比加一下 SELECT p.* 不就知道了？
rows?
【在 a********e 的大作中提到】
: I run the simple query in MS sql2012
: SELECT count(*) from PNTD.dbo.PEOPLE p
: LEFT JOIN PNTD.dbo.AUTHORS at ON at.PEOPLEID = p.PEOPLEID ;
: SELECT count(*) from PNTD.dbo.PEOPLE p
: the return are 586 and 560.
: why the left join return more rows? should the first query return less rows?
|
t****n
发帖数: 10724 | 3 because one person can write many books/articles. PeopleID in table Authors
is not the primary key. Most likely PeopleID + BookID is the primary key. |
D*J
发帖数: 141 | 4 left join will add rows from the first table where there is no match in the
second table. there must be some rows (26 most likely) in PEOPLE which do
not have corresponding row in the AUTHORS table. which means 26 people are
not authors, most likely.
rows?
【在 a********e 的大作中提到】
: I run the simple query in MS sql2012
: SELECT count(*) from PNTD.dbo.PEOPLE p
: LEFT JOIN PNTD.dbo.AUTHORS at ON at.PEOPLEID = p.PEOPLEID ;
: SELECT count(*) from PNTD.dbo.PEOPLE p
: the return are 586 and 560.
: why the left join return more rows? should the first query return less rows?
|
c*****d
发帖数: 6045 | 5 你的回答解释不了他了问题
如果是你说的情况，people表这样
p1, p1_name
p2, p2_name
p3, p3_name
author表这样
a1, p1
a2, p2
SELECT * from PNTD.dbo.PEOPLE p
LEFT JOIN PNTD.dbo.AUTHORS at ON at.PEOPLEID = p.PEOPLEID ;
返回
p1, a1
p2, a2
p3, null
他的查询
SELECT count(*) from PNTD.dbo.PEOPLE p
LEFT JOIN PNTD.dbo.AUTHORS at ON at.PEOPLEID = p.PEOPLEID ;
应该返回3
SELECT count(*) from PNTD.dbo.PEOPLE p
应该返回3
the
【在 D*J 的大作中提到】
: left join will add rows from the first table where there is no match in the
: second table. there must be some rows (26 most likely) in PEOPLE which do
: not have corresponding row in the AUTHORS table. which means 26 people are
: not authors, most likely.
:
: rows?
|
j**********2
发帖数: 1495 | 6 因为AUTHORS table里有重复的PEOPLEID吧，这些重复的ID和people table有multiple
mach |
s**********o
发帖数: 14359 | 7 1个人可以是多个AUTHOR啊，
p*, at.*
count的是JOIN出来的RECORD |
