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

4

CustomerID CustomerName CustomerAddress CustomerCity CustomerPostalCode CustomerState OrderID OrderDate CustomerID OrderID ProductID OrderedQuantity ProductID ProductDescription ProductFinish ProductStandardPrice ProductLineID

CUSTOMER ORDER ORDERLINE PRODUCT

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
• n 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

6

Creates a Cartesian product of rows in involved tables Finds the matching rows meeting the join condition(s) Identifies columns to be presented in the result table STEP 1 STEP 2 FROM WHERE SELECT STEP 3

SupplierID SupplierName

10 Walmart 20 Target 30 Best Buy

SELECT SupplierName, ShipmentDate FROM Supplier_T, Shipment_T WHERE Supplier_T.SupplierID=Shipment_T.SupplierID; SUPPLIER EQUI JOIN SQL STATEMENT

PROCESSING OF EQUI JOIN - SPECIFYING WITHIN THE WHERE CLAUSE

ShipmentID ShipmentDate SupplierID

1 10-10-2016 20 2 10-12-2016 30 3 11-01-2016

SHIPMENT

7

Step 1: FROM Supplier_T, Shipment_T Step 2: WHERE Supplier_T.SupplierID=Shipment_T.SupplierID Step 3: SELECT

SupplierName, ShipmentDate

PROCESSING STEPS OF IMPLICIT EQUI JOIN

SupplierID SupplierName ShipmentID ShipmentDate SupplierID

10 Walmart 1 10-10-2016 20 10 Walmart 2 10-12-2016 30 10 Walmart 3 11-01-2016 20 Target 1 10-10-2016 20 20 Target 2 10-12-2016 30 20 Target 3 11-01-2016 30 Best Buy 1 10-10-2016 20 30 Best Buy 2 10-12-2016 30 30 Best Buy 3 11-01-2016

SupplierID SupplierName ShipmentID ShipmentDate SupplierID

20 Target 1 10-10-2016 20 30 Best Buy 2 10-12-2016 30

SupplierName ShipmentDate

Target 10-10-2016 Best Buy 10-12-2016

• For each customer who placed an order, what is the

customer’s name and order number?

8

This query produces the same results as the previous equi-join example MS SQL and ORACLE support the JOIN …ON syntax without the keyword INNER

EQUI JOIN - SPECIFYING WITHIN THE FROM CLAUSE (INNER JOIN …. ON)

ON clause performs the equality check for joining columns of the two tables Join (explicit) involves multiple tables in the FROM clause

• For each customer who placed an order, what is the

customer’s name and order number?

SEL ELECT CT Cus ustom

• merI

erID, , Cust ustome

• merName

rName, , OrderID derID FROM OM Cus ustom

• mer_T

er_T INN NNER R JOIN Order_T er_T USIN ING (Cus ustom

• merI

erID) ORDER ER BY OrderID derID;

9

This query produces the same results as the previous equi-join example, except CustomerID column is not repeated INNER JOIN …USING syntax may not be supported by all RDBMS

EQUI JOIN - SPECIFYING WITHIN THE FROM CLAUSE (INNER JOIN…..USING)

USING (column name) clause performs the equality check for joining columns of the two tables with identical names Join (explicit) involves multiple tables in FROM clause You cannot use a qualifier in the SELECT clause for the joining column when INNER JOIN…USING syntax is used

• 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;

10

NATURAL JOIN

NATURAL JOIN performs the join using CustomerID column. CustomerID is the attribute that appears in both tables This query produces a result table in which CustomerID column is not repeated NATURAL JOIN is not supported by all RDBMS (Oracle and MySQL support natural join) You cannot use a qualifier in the SELECT clause for the joining column when NATURAL JOIN syntax is used NATURAL JOIN is very similar to INNER JOIN…USING, except the column name is not explicitly mentioned

11

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

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.

13

LEFT OUTER JOIN clause causes customer data to appear even if there is no corresponding order data

Unlike INNER join, this will include customer rows with no matching order rows

RIGHT OUTER JOIN is the reverse of LEFT OUTER JOIN

ON

LEFT OUTER JOIN…USING syntax can also be used

14

Left Outer Join Results

Result table indicates NULL values for columns with no matching row

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;

15

RIGHT OUTER JOIN clause causes

• rder 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

16

Visualization of inner join and

• uter join with

results returned in shaded area

Thank you !

Reference:

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