SQL (cont’d)

1. SQL (cont’d) CSE3330 Spring 2014 Chengkai Li

2. NULL values

3. NULL values are all different The following query looks for employees who earn the same as their supervisors. If the database does not contain salary information of an employee and the supervisor (null values), the employee doesn’t appear in query result. (Which is what we shall expect.) SELECT E1.fname, E1.lname FROM EMPLOYEE AS E1, EMPLOYEE AS E2 WHERE E1.superssn=E2.ssn AND E1.salary= E2.salary;

4. Except that NULL values are all equal in UNION/INTERSECT/EXCEPT/DISTINCT/GROUP BY SELECT COUNT(*) FROM ( SELECT E.superssn FROM EMPLOYEE AS E, DEPARTMENT AS D WHERE E.dno=D.dnumber AND D.dname=‘research’ INTERSECT SELECT E.superssn FROM EMPLOYEE AS E, WORKS_ON AS W WHERE E.ssn=W.essn AND W.hours > 30 ) AS T; EMPLOYEE DEPARTMENT WORKS_ON ssn superssn dno dnumber dname essn proj hours --------------------------------- ------------------------------ -------------------------- 111 NULL 1 1 research 111 1 20 112 NULL 2 2 sales 112 2 35 113 111 1 113 1 35

5. Except that NULL values are all equal in UNION/INTERSECT/EXCEPT/DISTINCT/GROUP BY All employees with “null” salary values belong to a group. SELECT salary, COUNT(*) FROM EMPLOYEE GROUP BY salary;

6. Aggregation Take out null values, before aggregates are calculated. (except COUNT(*)) Suppose there are null values in “salary”. SELECT AVG(salary) FROM EMPLOYEE; is different from SELECT SUM(salary)/COUNT(*) FROM EMPLOYEE; SELECT COUNT(salary) FROM EMPLOYEE; is different from SELECT COUNT(*) FROM EMPLOYEE;

7. The confusing DISTINCT (at least in MySQL) NULL values are all equal in DISTINCT, without COUNT. They are ignored in COUNT (DISTINCT …) • SELECT DISTINCT a, b FROM T; a b ----------- 1 1 2 NULL • SELECT COUNT (DISTINCT a, b) FROM T; COUNT (DISTINCT a, b) -------------------------------- 1 SELECT a, b FROM T; a b ----------- 1 1 2 NULL 2 NULL

8. Subqueries

9. Subqueries • A parenthesized SELECT statement (subquery) in another SELECT statement. • Can be in FROM clause • Can be in WHERE clause

10. Subquery in FROM • In place of a relation in the FROM clause, we can place another query, and then query its result. • use a tuple-variable to name tuples of the result. • Subquery in FROM is not mentioned in textbook, but is useful. • The SQL syntax on page 140 needs revision to include subquery in FROM SELECT FROM ( SELECT ... FROM … [WHERE …] [ GROUP BY … [HAVING …] ] [ORDER BY …] ) AS … [WHERE …] [ GROUP BY … [HAVING …]] [ORDER BY …]

11. Example SELECT S.sid, S.name, R.semester, R.year FROM students AS S, ( SELECT * FROM register WHERE grade=‘A’ ) AS R WHERE S.sid=R.sid SELECT MAX(total) FROM ( SELECT sid, COUNT(*) as total FROM register GROUP BY sid ) AS R

12. Subquery in WHERE • Subqueries in WHERE are also called nested queries • IN • EXISTS • > / < / = / >= / <= / < > ANY/ALL

13. SELECT statements embedded in other statements • UNION, INTERSECT, EXCEPT Q4: (SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION (SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith')

14. Correlated Subqueries • Subqueries in FROM must not be correlated SELECT * FROM Dependents AS D, ( SELECT * FROM Employee AS E WHERE E.SSN=D.ESSN ) AS R • Nested queries (Subqueries in WHERE) can be correlated

15. Nested Queries can be deep SELECT PNO FROM WORKS_ON WHERE ESSN IN (SELECT SSN FROM EMPLOYEE WHERE LNAME=‘Smith’ AND DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME= ‘Research’) );

16. MySQL doesn’t provide EXCEPT/MINUS and INTERSECT

17. A nice write-up at http://faculty.utpa.edu/chebotkoa/main/teaching/csci4333fall2013/slides/MySQL-set-operators.pdf However, complexity due to null value is not considered. Bag semantics is not considered either.

18. Rewrite EXCEPT and INTERSECT SELECT ssn FROM EMPLOYEE WHERE salary < 10000 EXCEPT SELECT essn FROM WORKS_ON WHERE hours > 30; Rewrite this query using IN, EXISTS, LEFT OUTER JOIN

19. Rewrite EXCEPT by IN SELECT ssn FROM EMPLOYEE WHERE salary < 10000 EXCEPT SELECT essn FROM WORKS_ON WHERE hours > 30; SELECT ssn FROM EMPLOYEE WHERE salary < 10000 AND ssn NOT IN (SELECT essn FROM WORKS_ON WHERE hours > 30);

20. Rewrite EXCEPT by EXISTS SELECT ssn FROM EMPLOYEE WHERE salary < 10000 EXCEPT SELECT essn FROM WORKS_ON WHERE hours > 30; SELECT ssn FROM EMPLOYEE WHERE salary < 10000 AND NOT EXISTS (SELECT essn FROM WORKS_ON WHERE hours > 30 AND essn=ssn);

21. Rewrite EXCEPT by LEFT OUTER JOIN SELECT ssn FROM EMPLOYEE WHERE salary < 10000 EXCEPT SELECT essn FROM WORKS_ON WHERE hours > 30; Incorrect rewriting: SELECT ssn FROM EMPLOYEE LEFT OUTER JOIN WORKS_ON ON ssn=essn WHERE salary < 10000 AND hours <= 30; WORKS_ON EMPLOYEE essn pno hours ssn salary ------------------------- --------------------- 111 1 31 111 5000 111 2 9

22. Rewrite EXCEPT by LEFT OUTER JOIN Correct rewriting: SELECT ssn FROM ((SELECT ssn FROM EMPLOYEE WHERE salary < 10000) AS R LEFT OUTER JOIN (SELECT essn FROM WORKS_ON WHERE hours > 30) AS T ON R.ssn=T.essn) WHERE T.essn IS NULL; WORKS_ON EMPLOYEE R T R left outer join T essn pno hours ssn salary ssn essn ssn essn ------------------------- --------------------- ------- ------- -------------------------- 111 1 31 111 5000 111 111 111 111 111 2 9 112 8000 112 112 112 1 20