CSCI 2141 – Intro to

Presentation on theme: "CSCI 2141 – Intro to"— Presentation transcript:

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;