Merging Multiple Queries Pertemuan 6

1. Merging Multiple QueriesPertemuan 6 Matakuliah : T0413/Current Popular IT II Tahun : 2007

2. AGENDA:•UNION•UNION with ORDERBY•INTERSECT•EXCEPT or MINUS•Examples Book: Mastering SQL by Martin Gruber Sybex (2000) Chapter : 13

3. UNION • Uniting multiple queries as one output result • Using UNION clause • SELECT snum, sname FROM Salespeople WHERE city = ‘London’ UNION SELECT cnum, cname FROM Customers WHERE city = ‘London’

4. UNION (cont’d) • The columns selected by the two statements are outputs as though they were one. • UNION will automatically eliminate duplicate rows from the output.

5. UNION (cont’d) • Using Strings and Expression with UNION • For example if you want to create a report: SELECT a.snum, sname, onum, ‘Highest on’, odate FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX(amt) FROM Orders c WHERE c.odate = b.odate) UNION SELECT a.snum, sname, onum, ‘Lowest on’, odate FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MIN(amt) FROM Orders c WHERE c.odate = b.odate)

6. UNION with ORDERBY • The data from the multiple queries are not output with any particular order. • To order the output from a UNION, we can use ORDER BY. SELECT a.snum, sname, onum, ‘Highest on’, odate FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX(amt) FROM Orders c WHERE c.odate = b.odate) UNION SELECT a.snum, sname, onum, ‘Lowest on’, odate FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MIN(amt) FROM Orders c WHERE c.odate = b.odate) ORDER BY 3;

7. INTERSECT • The INTERSECT operator finds the intersection of rows output by the two or more queries. • Example: SELECT snum FROM Orders a WHERE 1500.00 < (SELECT SUM(amt) FROM Orders b WHERE b.snum = a.snum) INTERSECT SELECT snum FROM Salespeople c WHERE 2 > (SELECT COUNT(*) FROM Customers d WHERE d.snum = c.snum); • The query above try to find which salesperson had more than $1500.00 total in current orders with less than two customers assigned.

8. EXCEPT or MINUS • The main idea of EXEPT is it takes two queries, A and B, and includes in the output only the rows from A that were not also produced by B. • In other words, it excludes rows produced by B but not by A, so the effect is that no rows output by query B are in the final output in any case. • EXCEPT is respective of the order in which queries are stated. • So the output will be different if the order of the queries are being switched.

9. Examples • SELECT snum FROM Orders a WHERE 1500.00 < (SELECT SUM(amt) FROM Orders b WHERE b.snum = a.snum) EXCEPT SELECT snum FROM Salespeople c WHERE 2 > (SELECT COUNT(*) FROM Customers d WHERE d.snum = c.snum);

10. Examples (cont’d) • SELECT snum FROM Salespeople c WHERE 2 > (SELECT COUNT(*) FROM Customers d WHERE d.snum = c.snum); EXCEPT SELECT snum FROM Orders a WHERE 1500.00 < (SELECT SUM(amt) FROM Orders b WHERE b.snum = a.snum)

11. Examples (cont’d) • The output of previous two queries are different, because the order of the queries using EXCEPT were switched. • The first query will produce output : Snum = 1001 and 1002 • While the second query will produce output : Snum = 1007 and 1020

12. End of Merging Multiple Queries Thank you