JOINS IN SQL By Rohit Dhanwani OBJECTIVES Define and use different - PowerPoint PPT Presentation

JOINS IN SQL By Rohit Dhanwani

OBJECTIVES • Define and use different types of joins • INNER JOIN • EQUI JOIN • NATURAL JOIN • OUTER JOIN • LEFT OUTER JOIN • RIGHT OUTER JOIN • FULL OUTER JOIN 2

PROCESSING MULTIPLE TABLES - JOIN • Join is a relational operation that causes two or more tables with a common domain to be combined into a single table or view • Joining columns (i.e., columns linking tables with a common domain) are used to define a join condition • Generally , the columns in a join condition are the primary key of one table and the foreign key of another table in a 1:M relationship (i.e., joining columns = common columns) • Occasionally , a join can be established using columns that share a common domain, but not the primary- foreign key relationship (i.e., joining columns ≠ common columns) • Join condition is specified in WHERE or FROM clause (one join condition for each pair of tables joined) 3

RELATIONAL DATA MODEL USED IN THE FOLLOWING MULTIPLE-TABLE QUERIES CUSTOMER CustomerID CustomerName CustomerAddress CustomerCity CustomerState CustomerPostalCode ORDER OrderID OrderDate CustomerID ORDERLINE OrderID ProductID OrderedQuantit y PRODUCT ProductID ProductDescription ProductFinish ProductStandardPrice ProductLineID 4

INNER JOIN • Inner join - a join in which the join condition contains a comparison operator (e.g., =, >,<) for the values of the joining columns . Returns matching rows (i.e. rows meeting the join condition) [the most popular join] • Equi-join – a special type of inner join where the equality operator is used in the join condition. That is, the join condition is based on equality between values in the joining columns. Joining columns may appear redundantly in the result table • Almost every join is an equi-join/inner join, because the join condition is based on the equality of two values — one from each of the tables being joined • Natural join – same as equi-join except the join is performed over columns with identical names . Plus, the result table contains only one column for each pair of joining columns 5

PROCESSING OF EQUI JOIN - SPECIFYING WITHIN THE WHERE CLAUSE STEP 2 STEP 1 STEP 3 WHERE SELECT FROM Identifies Creates a Cartesian Finds the matching columns to product of rows rows meeting be presented in in involved tables the join condition(s) the result table SupplierID SupplierName SUPPLIER 10 Walmart EQUI JOIN SQL STATEMENT 20 Target 30 Best Buy SELECT SupplierName, ShipmentDate FROM Supplier_T, Shipment_T ShipmentID ShipmentDate SupplierID SHIPMENT WHERE 1 10-10-2016 20 Supplier_T.SupplierID=Shipment_T.SupplierID; 2 10-12-2016 30 3 11-01-2016 6

PROCESSING STEPS OF IMPLICIT EQUI JOIN Step 1: FROM Supplier_T, Shipment_T SupplierID SupplierName ShipmentID ShipmentDate SupplierID 10 Walmart 1 10-10-2016 20 Step 3: SELECT 10 Walmart 2 10-12-2016 30 SupplierName, 10 Walmart 3 11-01-2016 ShipmentDate 20 Target 1 10-10-2016 20 20 Target 2 10-12-2016 30 SupplierName ShipmentDate 20 Target 3 11-01-2016 Target 10-10-2016 30 Best Buy 1 10-10-2016 20 Best Buy 10-12-2016 30 Best Buy 2 10-12-2016 30 30 Best Buy 3 11-01-2016 Step 2: WHERE Supplier_T.SupplierID=Shipment_T.SupplierID SupplierID SupplierName ShipmentID ShipmentDate SupplierID 20 Target 1 10-10-2016 20 30 Best Buy 2 10-12-2016 30 7

EQUI JOIN - SPECIFYING WITHIN THE FROM CLAUSE (INNER JOIN …. ON) • For each customer who placed an order, what is the customer’s name and order number? Join (explicit) involves multiple tables in the FROM clause ON clause performs the equality check for joining columns of the two tables This query produces the same results as the previous MS SQL and ORACLE support the JOIN equi-join example …ON syntax without the keyword INNER 8

EQUI JOIN - SPECIFYING WITHIN THE FROM CLAUSE (INNER JOIN…..USING) • For each customer who placed an order, what is the customer’s name and order number? Join (explicit) involves multiple tables in FROM clause SEL ELECT CT Cus ustom omerI erID, , Cust ustome omerName rName, , OrderID derID FROM OM Cus ustom omer_T er_T INN NNER R JOIN Order_T er_T USIN ING (Cus ustom omerI erID) ORDER ER BY OrderID derID; USING (column name) clause performs the equality check for This query produces the joining columns of the two tables same results as the previous with identical names equi-join example, except CustomerID column is not INNER JOIN …USING syntax may not be repeated supported by all RDBMS You cannot use a qualifier in the SELECT clause for the joining column when INNER JOIN…USING syntax is used 9

NATURAL JOIN • Join Customer and Order tables using Natural join. List all the columns from these tables SELECT * FROM Customer_T NATURAL JOIN Order_T ORDER BY OrderID; This query produces a result table NATURAL JOIN performs the in which CustomerID column join using CustomerID column. is not repeated CustomerID is the attribute that appears in both tables NATURAL JOIN is very similar to NATURAL JOIN is not supported by all RDBMS INNER (Oracle and MySQL support natural join) JOIN…USING, except the column You cannot use a qualifier in the SELECT clause for the name is not joining column when NATURAL JOIN syntax is used explicitly mentioned 10

NATURAL JOIN (Another Example) SELECT * FROM Item_T NATURAL JOIN Company_T; No explicit mention of joining columns Company_ID that exists in both tables is used to join the tables Company_ID column is not repeated in the result table 11

OUTER JOIN • Outer join – a join in which rows that do not have matching values in joining columns are nonetheless included in the result table (as opposed to inner join , in which rows must have matching values in order to appear in the result table) • Left Outer Join : returns all matching rows plus all rows of the left table that do not have a matching row in the right table • Right Outer Join : returns all matching rows plus all rows of the right table that do not have a matching row in the left table • Full Outer Join : returns all matching rows plus all rows of either table that do not have a matching row in the other table 12

LEFT OUTER JOIN • List the customer name, ID number, and order number for all customers. Include customer information even for customers that do not have any order. ON LEFT OUTER JOIN clause causes Unlike INNER join, this will include customer rows with customer data to appear even if no matching order rows there is no corresponding order data RIGHT OUTER JOIN is the reverse of LEFT OUTER JOIN LEFT OUTER JOIN…USING syntax can also be used 13

Left Outer Join Results Result table indicates NULL values for columns with no matching row 14 14

RIGHT OUTER JOIN • List the customer name, ID number, and order number for all orders. Include order number even for orders with no customer information available. SELECT Customer_T.CustomerID, CustomerName, OrderID FROM Customer_T RIGHT HT OUTER ER JOIN Order_T ON ON Customer_T.CustomerID=Order_T.CustomerID; RIGHT OUTER JOIN clause causes order data to appear even if there is no corresponding customer data Due to the referential integrity constraint, every order will have a valid customer ID. Hence, this query produces the same result table as the previous equi-join example 15

Visualization of inner join and outer join with results returned in shaded area 16

Thank you !

Reference: Hoffer, Jeffrey A., V. Ramesh, and Heikki Topi " Modern database management " 10 th edition