CSCI 2141 Intro to Database Systems Advanced SQL Joins Joins Table 81 SQL Join Expression Styles 3 Sample Database CROSS JOIN Returns the Cartesian product of tables Example SELECT FROM CUSTOMER CUSTOMER2 ID: 768142
Download Presentation The PPT/PDF document "CSCI 2141 – Intro to" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
CSCI 2141 – Intro to Database Systems Advanced SQL –Joins
Joins
Table 8.1 - SQL Join Expression Styles 3
Sample Database
CROSS JOINReturns the Cartesian product of tablesExample: SELECT * FROM CUSTOMER, CUSTOMER_2; Returns 2x4 = 8 rows
CROSS JOIN SELECT * FROM CUSTOMER CROSS JOIN CUSTOMER_2; Gives the same result i.e. 8 rows Specific attributes can also be returned Example:SELECT C.CUST_FNAME, C2.CUST_FNAME FROM CUSTOMER AS C CROSS JOIN CUSTOMER_2 AS C2;
INNER JOINTraditional JOIN in which only rows meeting a given criteria are returned JOIN criteria can be an equality condition Also called a NATURAL JOIN or EQUIJOIN JOIN criteria can be an inequality conditionAlso called THETA JOIN
INNER JOINSeveral styles allowedNATURAL JOIN Old-style JOIN JOIN USING JOIN ON
INNER JOIN – NATURAL JOINNatural join compares all common columns Suppresses duplicate columns SELECT * FROM CUSTOMER NATURAL JOIN CUSTOMER_2; UPDATE CUSTOMER_2 SET CUST_NUM=1001 WHERE CUST_FNAME = ‘Juan’; SELECT * FROM CUSTOMER NATURAL JOIN CUSTOMER_2;
INNER JOIN – NATURAL JOINNot limited to two tables in Natural JoinExample: SELECT * FROM CUSTOMER NATURAL JOIN CUSTOMER_2 NATURAL JOIN INVOICE;
INNER JOIN – Old-Style JOIN SELECT * FROM CUSTOMER C, CUSTOMER_2 C2 WHERE C.CUST_NUM = C2.CUST_NUM AND C.CUST_LNAME = C2.CUST_LNAME AND C.CUST_FNAME = C2.CUST_FNAME;
INNER JOIN – JOIN USING JOIN USING uses common attribute names from two tables SELECT * FROM CUSTOMER JOIN CUSTOMER_2 USING (CUST_NUM, CUST_LNAME, CUST_FNAME); This produces the same result as the Natural Join SELECT * FROM CUSTOMER NATURAL JOIN CUSTOMER_2;
INNER JOIN – JOIN USINGMultiple tables can also be joined with JOIN USINGExample SELECT * FROM CUSTOMER JOIN CUSTOMER_2 USING (CUST_NUM, CUST_LNAME, CUST_FNAME) JOIN INVOICE USING (CUST_NUM);
INNER JOIN – JOIN ONJOIN ON can be used to join tables with no common attribute names (or different attribute names)Syntax: SELECT columnlist FROM table1 JOIN table2 ON join-conditionA table qualifier is required for using JOIN ON
INNER JOIN – JOIN ONExample: SELECT * FROM CUSTOMER C JOIN CUSTOMER_2 C2 ON C.CUST_NUM = C2.CUST_NUM; SELECT * FROM CUSTOMER C JOIN CUSTOMER_2 C2 ON (C.CUST_NUM = C2.CUST_NUM) JOIN INVOICE ON C.CUST_NUM = INVOICE.CUST_NUM; Note that JOIN ON lets you perform a join even when tables do not share a common attribute name
OUTER JOINSOuter join returns the rows matching the join condition as well as rows with unmatched valuesThree types of outer joins are defined: LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN
LEFT OUTER JOINExample: SELECT * FROM CUSTOMER C LEFT OUTER JOIN INVOICE I ON (C.CUST_NUM = I.CUST_NUM); SELECT * FROM INVOICE I LEFT OUTER JOIN CUSTOMER C ON (C.CUST_NUM = I.CUST_NUM);
Joining Tables With Recursive Joins Recursive query : Table is joined to itself using alias The manager is an employee An employee may have a manager One manager may manage many employees 18
Joining Tables With Recursive JoinsExample 1: List all employees by their managers SELECT E.EMP_NUM, E.EMP_FNAME, E.EMP_LNAME, E.EMP_MGR FROM EMP E, EMP M WHERE E.EMP_MGR = M.EMP_NUM ORDER BY E.EMP_MGR; SELECT EMP_NUM, EMP_FNAME, EMP_LNAME, EMP_MGRFROM EMP WHERE EMP_MGR IS NOT NULL ORDER BY EMP_MGR; Q: What if you want to show the Manager’s last name as well?
Joining Tables With Recursive JoinsSELECT E.EMP_NUM, E.EMP_FNAME, E.EMP_LNAME, E.EMP_MGR, M.EMP_LNAME FROM EMP E, EMP M WHERE E.EMP_MGR = M.EMP_NUM ORDER BY E.EMP_MGR;