/*列出所有父母的姓名和对于的孩子数目(没有孩子的对于数目为0)*/
SELECT pname ,
count(c.cno)
FROM parents p LEFT JOIN children c
ON p.pno=c.pno
GROUP BY p.pname
;
/*列出所有没有父母信息的孩子的姓名*/
SELECT cname
FROM children
WHERE children.pno NOT IN (
SELECT pno FROM parents
) OR children.pno IS NULL;
或者:
SELECT cname
FROM children
WHERE NOT EXISTS (
SELECT * FROM parents WHERE children.pno=parents.pno
);
/*列车所有没有孩子的父母的姓名*/
SELECT pname
FROM parents
WHERE parents.pno NOT IN(
SELECT DISTINCT(children.pno) FROM children WHERE children.pno IS NOT NULL
);
或者:
SELECT pname
FROM parents
WHERE NOT EXISTS(
SELECT * FROM children WHERE children.pno=parents.pno
);