SQL复习-dml-setops

UNION 和在where里面写两个条件然后or起来的区别在于union会把重复的去掉，也就是用or的话如果是两个都有就会出现几次（不用distinct的话）

INTERSECT和EXCEPT不被支持，前者用join table 或者in 后者用not in

存在，所有

SELECT D.name FROM Departments D WHERE D.budget > SOME( SELECT D2.budget FROM Departments D2 WHERE D2.department_id IN (SELECT W.department_id FROM Works_in W WHERE W.employee_id = 4) );

SELECT D.name FROM Departments D WHERE D.budget >= ALL (SELECT D2.budget FROM Departments D2);

某个用exist做的判断

SELECT E.name FROM E.Employees E WHERE EXISTS (SELECT * FROM Works_in W WHERE W.department_id = 1 AND E.employee_id = W.employee_id);

这种情况下就不要吧把两个table并起来然后再where =了

关于NULL VALUE

SELECT name FROM Employees WHERE salary IS NULL；

5 + null returns null.对于四则运算

Both 5 < null , null = null return UNKNOWN.对于比较

对于 aggregate operations except COUNT(*) ignore tuples with null values on the aggregated attributes.