JOIN in SQL Query for Multiple Table Relationships

undefined
 
CSE 344
 
JANUARY 12
TH
 –JOINS
 
JOIN: INTRO
 
•
T
h
e
 
J
O
I
N
 
i
s
 
t
h
e
 
w
a
y
 
w
e
 
i
n
d
i
c
a
t
e
 
i
n
 
a
 
q
u
e
r
y
 
h
o
w
m
u
l
t
i
p
l
e
 
t
a
b
l
e
s
 
a
r
e
 
r
e
l
a
t
e
d
.
•
Example, if we want all of the products and their
relevant company information, we need to 
join
those two tables.
•
The result of the join is all of the relevant
information from both tables
•
Join occurs based on the join condition.
•
This allows us to access information that comes
from multiple tables
 
JOINS IN SQL
Retrieve all Japanese products that cost < $150
 
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
 
JOINS IN SQL
Retrieve all Japanese products that cost < $150
 
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
SELECT 
pname, price
FROM   
Product, Company
WHERE  
...
 
JOINS IN SQL
Retrieve all Japanese products that cost < $150
 
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
SELECT 
pname, price
FROM   
Product, Company
WHERE  
manufacturer=cname AND
       country='Japan' AND price < 150
 
JOINS IN SQL
 
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
Retrieve all USA companies
that manufacture “gadget” products
 
JOINS IN SQL
 
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
Retrieve all USA companies
that manufacture “gadget” products
SELECT DISTINCT 
cname
FROM   
Product, Company
WHERE  
country='USA' AND category = 'gadget'
       AND manufacturer = cname
Why
DISTINCT
?
 
JOINS IN SQL
 
T
h
e
 
s
t
a
n
d
a
r
d
 
j
o
i
n
 
i
n
 
S
Q
L
 
i
s
 
s
o
m
e
t
i
m
e
s
 
c
a
l
l
e
d
 
a
n
 
i
n
n
e
r
 
j
o
i
n
•
E
a
c
h
 
r
o
w
 
i
n
 
t
h
e
 
r
e
s
u
l
t
 
m
u
s
t
 
c
o
m
e
 
f
r
o
m
 
b
o
t
h
 
t
a
b
l
e
s
 
i
n
 
t
h
e
j
o
i
n
S
o
m
e
t
i
m
e
s
 
w
e
 
w
a
n
t
 
t
o
 
i
n
c
l
u
d
e
 
r
o
w
s
 
f
r
o
m
 
o
n
l
y
 
o
n
e
 
o
f
 
t
h
e
 
t
w
o
t
a
b
l
e
:
 
o
u
t
e
r
 
j
o
i
n
 
INNER JOIN
Retrieve employees and their sales
 
Employee(
id
, name)
Sales(
employeeID
, productID)
 
INNER JOIN
Retrieve employees and their sales
SELECT 
*
FROM   
Employee E, Sales S
WHERE  
E.id = S.employeeID
 
Employee(
id
, name)
Sales(
employeeID
, productID)
 
INNER JOIN
Retrieve employees and their sales
SELECT 
*
FROM   
Employee E, Sales S
WHERE  
E.id = S.employeeID
 
Employee(
id
, name)
Sales(
employeeID
, productID)
 
INNER JOIN
Retrieve employees and their sales
SELECT 
*
FROM   
Employee E, Sales S
WHERE  
E.id = S.employeeID
 
Employee(
id
, name)
Sales(
employeeID
, productID)
Jill is
missing
 
INNER JOIN
Retrieve employees and their sales
SELECT 
*
FROM   
Employee E
       
INNER JOIN
       Sales S
    
ON 
E.id = S.employeeID
 
Employee(
id
, name)
Sales(
employeeID
, productID)
Jill is
missing
Alternative
syntax
 
OUTER
 JOIN
Retrieve employees and their sales
SELECT 
*
FROM   
Employee E
       
LEFT OUTER JOIN
       Sales S
    
ON 
E.id = S.employeeID
 
Employee(
id
, name)
Sales(
employeeID
, productID)
Jill is
present
 
(INNER) JOINS
SELECT DISTINCT 
cname
FROM   
Product, Company
WHERE  
country='USA' AND category = 'gadget'
       AND manufacturer = cname
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
-- manufacturer is foreign key to Company
 
(INNER) JOINS
SELECT DISTINCT 
cname
FROM   
Product, Company
WHERE  
country='USA' AND category = 'gadget'
       AND manufacturer = cname
 
Product
 
Company
 
(INNER) JOINS
SELECT DISTINCT 
cname
FROM   
Product, Company
WHERE  
country='USA' AND category = 'gadget'
       AND manufacturer = cname
 
Product
 
Company
 
(INNER) JOINS
SELECT DISTINCT 
cname
FROM   
Product, Company
WHERE  
country='USA' AND category = 'gadget'
       AND manufacturer = cname
 
Product
 
Company
 
(INNER) JOINS
SELECT DISTINCT cname
FROM   
Product, Company
WHERE  
country='USA' AND category = 'gadget'
       AND manufacturer = cname
 
Product
 
Company
 
(INNER) JOINS
SELECT DISTINCT 
cname
FROM   
Product, Company
WHERE  
country='USA' AND category = 'gadget'
       AND manufacturer = cname
 
Product
 
Company
 
(INNER) JOINS
 
Product
 
Company
SELECT DISTINCT 
cname
FROM   
Product, Company
WHERE  
country='USA' AND category = 'gadget'
       AND manufacturer = cname
 
(INNER) JOINS
SELECT DISTINCT cname
FROM   Product, Company
WHERE  country='USA' AND category = 'gadget'
       AND manufacturer = cname
SELECT DISTINCT 
cname
FROM
   Product 
JOIN 
Company 
ON
       
country = 'USA' 
AND
 category = 'gadget'
       
AND 
manufacturer = cname
SELECT
 
DISTINCT
 cname
FROM
   Product, Company
WHERE
  country='USA' AND category = 'gadget'
       AND manufacturer = cname
for x1 in R1:
  for x2 in R2: 
    ...
        for xm in Rm: 
          if Cond(x1, x2…):
            output(x1.a1, x2.a2, … xm.am)   
(INNER) JOINS
SELECT  
x1.a1, x2.a2, … xm.am
FROM
    R1 as x1, R2 as x2, … Rm as xm
WHERE
   Cond
This is called nested loop semantics since we are
interpreting what a join means using a nested loop
 
ANOTHER EXAMPLE
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
-- manufacturer is foreign key to Company
Retrieve all USA companies that
manufacture products in both ‘gadget’ and
‘photography’ categories
 
ANOTHER EXAMPLE
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
-- manufacturer is foreign key to Company
Retrieve all USA companies that
manufacture products in both ‘gadget’ and
‘photography’ categories
SELECT
 
DISTINCT
 z.cname
FROM
 Product x, Company z
WHERE
 z.country = ’USA’
  AND x.manufacturer = z.cname
  AND x.category = 'gadget’
  AND x.category = 'photography;
Does this
work?
 
ANOTHER EXAMPLE
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
-- manufacturer is foreign key to Company
Retrieve all USA companies that
manufacture products in both ‘gadget’ and
‘photography’ categories
SELECT
 
DISTINCT
 z.cname
FROM
 Product x, Company z
WHERE
 z.country = ’USA’
  AND x.manufacturer = z.cname
  AND (x.category = 'gadget’
       OR x.category = 'photography);
What about
this?
 
ANOTHER EXAMPLE
Product(
pname
, price, category, manufacturer)
Company(
cname
, country)
-- manufacturer is foreign key to Company
Retrieve all USA companies that
manufacture products in both ‘gadget’ and
‘photography’ categories
SELECT
 
DISTINCT
 z.cname
FROM
 Product x, Product y, Company z
WHERE
 z.country = ’USA’
  AND x.manufacturer = z.cname
  AND y.manufacturer = z.cname
  AND x.category = 'gadget’
  AND y.category = 'photography;
Need to
 include
Product twice!
 
SELF-JOINS AND
TUPLE VARIABLES
 
F
i
n
d
 
U
S
A
 
c
o
m
p
a
n
i
e
s
 
t
h
a
t
 
m
a
n
u
f
a
c
t
u
r
e
 
b
o
t
h
p
r
o
d
u
c
t
s
 
i
n
 
t
h
e
 
‘
g
a
d
g
e
t
s
’
 
a
n
d
 
‘
p
h
o
t
o
’
 
c
a
t
e
g
o
r
y
J
o
i
n
i
n
g
 
P
r
o
d
u
c
t
 
w
i
t
h
 
C
o
m
p
a
n
y
 
i
s
 
i
n
s
u
f
f
i
c
i
e
n
t
:
n
e
e
d
 
t
o
 
j
o
i
n
 
P
r
o
d
u
c
t
,
 
w
i
t
h
 
P
r
o
d
u
c
t
,
 
a
n
d
 
w
i
t
h
C
o
m
p
a
n
y
 
W
h
e
n
 
a
 
r
e
l
a
t
i
o
n
 
o
c
c
u
r
s
 
t
w
i
c
e
 
i
n
 
t
h
e
 
F
R
O
M
c
l
a
u
s
e
 
w
e
 
c
a
l
l
 
i
t
 
a
 
s
e
l
f
-
j
o
i
n
;
 
i
n
 
t
h
a
t
 
c
a
s
e
 
w
e
m
u
s
t
 
u
s
e
 
t
u
p
l
e
 
v
a
r
i
a
b
l
e
s
 
(
w
h
y
?
)
 
SELF-JOINS
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
Product
 
Company
 
SELF-JOINS
 
Product
 
Company
 
x
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
SELF-JOINS
 
Product
 
Company
 
x
 
y
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
SELF-JOINS
 
Product
 
Company
 
x
 
y
 
z
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
SELF-JOINS
 
Product
 
Company
 
x
 
y
 
z
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
SELF-JOINS
 
Product
 
Company
 
x
 
y
 
z
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
SELF-JOINS
 
Product
 
Company
 
x
 
y
 
z
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
SELF-JOINS
 
Product
 
Company
 
x
 
y
 
z
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
SELF-JOINS
 
Product
 
Company
 
x
 
y
 
z
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
SELF-JOINS
 
Product
 
Company
 
x
 
y
 
z
SELECT DISTINCT 
z.cname
FROM
   Product x, Product y, Company z
WHERE
  z.country = ‘USA’
       AND x.category = ‘gadget’
       AND y.category = ‘photo’
       AND x.manufacturer = z.cname
       AND y.manufacturer = z.cname;
 
OUTER JOINS
SELECT 
Product.name, Purchase.store
FROM
   Product, Purchase
WHERE
  Product.name = Purchase.prodName
We want to include products that are never sold,
but some are not listed!  Why?
 
Product(
name
, category)
Purchase(prodName, store)
-- prodName is foreign key
 
OUTER JOINS
 
SELECT
 
Product.name, Purchase.store
 
FROM
   Product 
LEFT
 
OUTER
 
JOIN
 
Purchase 
ON
        Product.name = Purchase.prodName
 
Product(
name
, category)
Purchase(prodName, store)
-- prodName is foreign key
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
JOIN
 Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
LEFT OUTER JOIN 
Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
LEFT OUTER JOIN 
Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
Product
 
Purchase
 
SELECT
 Product.name, Purchase.store
 
FROM
   Product 
FULL
 
OUTER
 
JOIN
 
Purchase 
ON
        Product.name = Purchase.prodName
 
Output
 
OUTER JOINS
 
 
L
e
f
t
 
o
u
t
e
r
 
j
o
i
n
:
•
Include tuples from 
tableA 
even if no match
R
i
g
h
t
 
o
u
t
e
r
 
j
o
i
n
:
•
Include tuples from 
tableB 
even if no match
F
u
l
l
 
o
u
t
e
r
 
j
o
i
n
:
•
Include tuples from both even if no match
 
I
n
 
a
l
l
 
c
a
s
e
s
:
•
Patch tuples without matches using 
NULL
tableA 
(LEFT/RIGHT/FULL)
 
OUTER
 
JOIN
 
tableB 
ON
 
p
 
GROUPING AND
AGGREGATION
 
Purchase(product, price, quantity)
 
Find total quantities for all sales over $1, by product.
GROUPING AND AGGREGATION
SELECT
 
  product, Sum(quantity) AS TotalSales
FROM
     Purchase
WHERE
    price > 1
GROUP BY
 product
OTHER EXAMPLES
What does
it return?
Compare these
two queries:
 
NEED TO BE
CAREFUL…
 
NEED TO BE
CAREFUL…
 
NEED TO BE
CAREFUL…
 
NEED TO BE
CAREFUL…
 
NEED TO BE
CAREFUL…
Everything in SELECT must be
either a GROUP-BY attribute, or an aggregate
 
GROUPING AND
AGGREGATION
 
Purchase(product, price, quantity)
SELECT
 
  product, Sum(quantity) AS TotalSales
FROM
     Purchase
WHERE
    price > 1
GROUP BY
 product
 
How is this query processed?
 
Find total quantities for all sales over $1, by product.
 
GROUPING AND
AGGREGATION
 
Purchase(product, price, quantity)
SELECT
 
  product, Sum(quantity) AS TotalSales
FROM
     Purchase
WHERE
    price > 1
GROUP BY
 product
 
Find total quantities for all sales over $1, by product.
SELECT
 
  product, Sum(quantity) AS TotalSales
FROM
     Purchase
GROUP BY
 product
 
Do these queries return the same number of rows? Why?
 
GROUPING AND
AGGREGATION
 
Purchase(product, price, quantity)
SELECT
 
  product, Sum(quantity) AS TotalSales
FROM
     Purchase
WHERE
    price > 1
GROUP BY
 product
 
Find total quantities for all sales over $1, by product.
SELECT
 
  product, Sum(quantity) AS TotalSales
FROM
     Purchase
GROUP BY
 product
 
Do these queries return the same number of rows? Why?
Empty groups are removed, hence
first query may return fewer groups
GROUPING AND
AGGREGATION
CSE 344 - 
2017au
65
65
1. Compute the 
FROM
 and 
WHERE
 clauses.
2. Group by the attributes in the 
GROUPBY
3. Compute the 
SELECT
 clause: 
    grouped attributes and aggregates.
FWGS
 
TM
1,2: FROM, WHERE
66
66
SELECT
 
  product, Sum(quantity) 
AS
 TotalSales
FROM
     Purchase
WHERE
    price > 1
GROUP BY
 product
FWGS
WHERE price > 1
 
3,4. GROUPING, SELECT
 
67
67
SELECT
 
  product, Sum(quantity) 
AS
 TotalSales
FROM
     Purchase
WHERE
    price > 1
GROUP BY
 product
FWGS

								

								
								

										

												
Slide Note

												

										

								


								
						

							
						


							

								

									
										
Embed
									
									
										
Share
									

									
									
									

								

								
							


							

							 
 							 
 							 
						  


							
							

								
JOIN in SQL is a crucial concept that helps combine data from multiple tables based on specified conditions. This process allows for retrieving relevant information by linking tables together. In the provided scenario, we explore how to retrieve specific data by joining the Product and Company tables and executing queries to fetch Japanese products under $150 and USA companies manufacturing gadget products.


								
    
																		
  •   SQL Query
    • 
																	
  •    JOIN
    • 
																	
  •    Table Relationships
    • 
																	
  •    Database Management
    • 
																	
  •    Data Retrieval
    • 
																

								

								

									

										nmcla
										

											 nmcla Follow
											

										

									

									
Uploaded on Oct 07, 2024 | 0 Views

								

								


								

									
Download Presentation

									
								

								
Please find below an Image/Link to download the presentation.

								
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.


									
									

									


										 
										 
										 
										 

									

									

									

								
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.

								

									 
									
Download Presentation
								

								
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.


								

									
 E N D 

								


							
Presentation Transcript

							

							
  1. CSE 344 JANUARY 12TH   JOINS
    2. 
            
            
            
  2. JOIN: INTRO     The JOIN is the way we indicate in a query how  multiple tables are related.     Example, if we want all of the products and their  relevant company information, we need to join  those two tables.     The result of the join is all of the relevant  information from both tables     Join occurs based on the join condition.     This allows us to access information that comes  from multiple tables
    3. 
            
            
            
  3. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all Japanese products that cost < $150
    4. 
            
            
            
  4. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all Japanese products that cost < $150 SELECT pname, price FROM   Product, Company WHERE  ...
    5. 
            
            
            
  5. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all Japanese products that cost < $150 SELECT pname, price FROM   Product, Company WHERE  manufacturer=cname AND        country='Japan' AND price < 150
    6. 
            
            
            
  6. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all USA companies  that manufacture    gadget    products
    7. 
            
            
            
  7. Product(pname, price, category, manufacturer) Company(cname, country) JOINS IN SQL pname MultiTouch SingleTouch Gizom SuperGizmo price 199.99 49.99 50 250.00 category gadget photography gadget gadget manufacturer Canon Canon GizmoWorks GizmoWorks cname GizmoWorks Canon Hitachi country USA Japan Japan Retrieve all USA companies  that manufacture    gadget    products Why DISTINCT? SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname
    8. 
            
            
            
  8. JOINS IN SQL The standard join in SQL is sometimes called an inner join     Each row in the result must come from both tables in the  join Sometimes we want to include rows from only one of the two  table: outer join
    9. 
            
            
            
  9. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Retrieve employees and their sales
    10. 
            
            
            
  10. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Retrieve employees and their sales SELECT *  FROM   Employee E, Sales S  WHERE  E.id = S.employeeID
    11. 
            
            
            
  11. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Retrieve employees and their sales id 1 1 2 name Joe Joe Jack empolyeeID 1 1 2 productID 344 355 544 SELECT *  FROM   Employee E, Sales S  WHERE  E.id = S.employeeID
    12. 
            
            
            
  12. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Jill is missing Retrieve employees and their sales id 1 1 2 name Joe Joe Jack empolyeeID 1 1 2 productID 344 355 544 SELECT *  FROM   Employee E, Sales S  WHERE  E.id = S.employeeID
    13. 
            
            
            
  13. Employee(id, name) Sales(employeeID, productID) INNER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Jill is missing Retrieve employees and their sales Alternative syntax id 1 1 2 name Joe Joe Jack empolyeeID 1 1 2 productID 344 355 544 SELECT *  FROM   Employee E        INNER JOIN        Sales S      ON E.id = S.employeeID
    14. 
            
            
            
  14. Employee(id, name) Sales(employeeID, productID) OUTER JOIN Employee id 1 2 3 Sales employeeID 1 1 2 name Joe Jack Jill productID 344 355 544 Jill is present Retrieve employees and their sales id 1 1 2 3 name Joe Joe Jack Jill empolyeeID 1 1 2 NULL productID 344 355 544 NULL SELECT *  FROM   Employee E        LEFT OUTER JOIN        Sales S      ON E.id = S.employeeID
    15. 
            
            
            
  15. (INNER) JOINS Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname
    16. 
            
            
            
  16. (INNER) JOINS SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi
    17. 
            
            
            
  17. (INNER) JOINS SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi
    18. 
            
            
            
  18. (INNER) JOINS SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi
    19. 
            
            
            
  19. (INNER) JOINS SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA
    20. 
            
            
            
  20. (INNER) JOINS SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi
    21. 
            
            
            
  21. (INNER) JOINS SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname Product Company cname country pname category manufacturer GizmoWorks USA Gizmo gadget GizmoWorks Canon Japan Camera Photo Hitachi Hitachi Japan OneClick Photo Hitachi
    22. 
            
            
            
  22. (INNER) JOINS SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget'        AND manufacturer = cname        AND manufacturer = cname SELECT DISTINCT cname FROM   Product, Company WHERE  country='USA' AND category = 'gadget' SELECT DISTINCT cname FROM   Product JOIN Company ON         country = 'USA' AND category = 'gadget'         AND manufacturer = cname
    23. 
            
            
            
  23. (INNER) JOINS SELECT  x1.a1, x2.a2,     xm.am FROM    R1 as x1, R2 as x2,     Rm as xm WHERE   Cond for x1 in R1:   for x2 in R2:      ...         for xm in Rm:  if Cond(x1, x2   ): output(x1.a1, x2.a2,     xm.am)    This is called nested loop semantics since we are interpreting what a join means using a nested loop
    24. 
            
            
            
  24. ANOTHER EXAMPLE Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company Retrieve all USA companies that manufacture products in both    gadget    and    photography    categories
    25. 
            
            
            
  25. ANOTHER EXAMPLE Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company Retrieve all USA companies that manufacture products in both    gadget    and    photography    categories SELECT DISTINCT z.cname FROM Product x, Company z WHERE z.country =    USA       AND x.manufacturer = z.cname   AND x.category = 'gadget      AND x.category = 'photography; Does this work?
    26. 
            
            
            
  26. ANOTHER EXAMPLE Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company Retrieve all USA companies that manufacture products in both    gadget    and    photography    categories SELECT DISTINCT z.cname FROM Product x, Company z WHERE z.country =    USA       AND x.manufacturer = z.cname   AND (x.category = 'gadget           OR x.category = 'photography); What about this?
    27. 
            
            
            
  27. ANOTHER EXAMPLE Product(pname, price, category, manufacturer) Company(cname, country) -- manufacturer is foreign key to Company Retrieve all USA companies that manufacture products in both    gadget    and    photography    categories SELECT DISTINCT z.cname FROM Product x, Product y, Company z WHERE z.country =    USA       AND x.manufacturer = z.cname    AND y.manufacturer = z.cname   AND x.category = 'gadget      AND y.category = 'photography; Need to include Product twice!
    28. 
            
            
            
  28. SELF-JOINS AND  TUPLE VARIABLES Find USA companies that manufacture both  products in the    gadgets    and    photo    category Joining Product with Company is insufficient:  need to join Product, with Product, and with  Company When a relation occurs twice in the FROM  clause we call it a self-join; in that case we  must use tuple variables (why?)
    29. 
            
            
            
  29. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; Product  Company pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks
    30. 
            
            
            
  30. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; Product  Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks
    31. 
            
            
            
  31. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; Product  Company x pname category manufacturer cname country y Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks
    32. 
            
            
            
  32. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; z Product  Company x pname category manufacturer cname country y Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks
    33. 
            
            
            
  33. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; z Product  Company x pname category manufacturer cname country y Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks
    34. 
            
            
            
  34. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; z Product  Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA y SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks
    35. 
            
            
            
  35. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; z Product  Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA y SingleTouch photo Hitachi Hitachi Japan MultiTouch Photo GizmoWorks
    36. 
            
            
            
  36. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; z Product  Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan y MultiTouch Photo GizmoWorks
    37. 
            
            
            
  37. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; z Product  Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan y MultiTouch Photo GizmoWorks x.pname x.category x.manufacturer y.pname y.category y.manufacturer z.cname z.country Gizmo gadget GizmoWorks MultiTouch Photo GizmoWorks GizmoWorks USA
    38. 
            
            
            
  38. SELF-JOINS SELECT DISTINCT z.cname FROM   Product x, Product y, Company z WHERE  z.country =    USA            AND x.category =    gadget           AND y.category =    photo           AND x.manufacturer = z.cname        AND y.manufacturer = z.cname; z Product  Company x pname category manufacturer cname country Gizmo gadget GizmoWorks GizmoWorks USA SingleTouch photo Hitachi Hitachi Japan y MultiTouch Photo GizmoWorks x.pname x.category x.manufacturer y.pname y.category y.manufacturer z.cname z.country Gizmo gadget GizmoWorks MultiTouch Photo GizmoWorks GizmoWorks USA
    39. 
            
            
            
  39. OUTER JOINS Product(name, category) Purchase(prodName, store)  -- prodName is foreign key SELECT Product.name, Purchase.store FROM   Product, Purchase WHERE  Product.name = Purchase.prodName We want to include products that are never sold, but some are not listed!  Why?
    40. 
            
            
            
  40. OUTER JOINS Product(name, category) Purchase(prodName, store)  -- prodName is foreign key SELECT Product.name, Purchase.store  FROM   Product LEFT OUTER JOIN Purchase ON         Product.name = Purchase.prodName
    41. 
            
            
            
  41. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz
    42. 
            
            
            
  42. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz
    43. 
            
            
            
  43. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output
    44. 
            
            
            
  44. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output
    45. 
            
            
            
  45. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output
    46. 
            
            
            
  46. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output
    47. 
            
            
            
  47. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output Camera Ritz
    48. 
            
            
            
  48. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output Camera Ritz Camera Wiz
    49. 
            
            
            
  49. SELECT Product.name, Purchase.store  FROM   Product JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output Camera Ritz Camera Wiz
    50. 
            
            
            
  50. SELECT Product.name, Purchase.store  FROM   Product LEFT OUTER JOIN Purchase ON         Product.name = Purchase.prodName Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Output Camera Ritz Camera Wiz
    51. 
            
            
            
						

						

					

					
					
					

						
Related

						

						


							
							
							
							

						

							

									

      

        
          

            

              Different Types of SQL Joins
            

            

              
Different Types of SQL Joins

              
anatoly

            

          

        
      


     

      

        
          

            

              Query Optimization in Database Management Systems
            

            

              
Query Optimization in Database Management Systems

              
johan

            

          

        
      


     

      

        
          

            

              Joins in DBMS: INNER JOIN, LEFT JOIN, and Examples
            

            

              
Joins in DBMS: INNER JOIN, LEFT JOIN, and Examples

              
laoise

            

          

        
      


     

      

        
          

            

              Introduction to SQL: Learn the Basics and Beyond
            

            

              
Introduction to SQL: Learn the Basics and Beyond

              
ford

            

          

        
      


     

      

        
          

            

              SQL: Major Aspects and Functionality in Database Applications
            

            

              
SQL: Major Aspects and Functionality in Database Applications

              
nojus

            

          

        
      


     

      

        
          

            

              SQL Functions for Database Queries
            

            

              
SQL Functions for Database Queries

              
kira

            

          

        
      


     

      

        
          

            

              Comprehensive Overview of SQL Commands and Language Categories
            

            

              
Comprehensive Overview of SQL Commands and Language Categories

              
orin

            

          

        
      


     

      

        
          

            

              Identifying Completeness of Query Answers in Incomplete Databases
            

            

              
Identifying Completeness of Query Answers in Incomplete Databases

              
crystal

            

          

        
      


     

      

        
          

            

              Turkish Statistical Institute SQL Fundamentals Overview
            

            

              
Turkish Statistical Institute SQL Fundamentals Overview

              
alm_sin

            

          

        
      


     

      

        
          

            

              SQL Part II Lecture Summary: Nested Queries, Joins, and Updates for Database Applications
            

            

              
SQL Part II Lecture Summary: Nested Queries, Joins, and Updates for Database Applications

              
taul_du

            

          

        
      


     

      

        
          

            

              Table Joins in SQL
            

            

              
Table Joins in SQL

              
giuliet

            

          

        
      


     

      

        
          

            

              SQL JOIN: A Comprehensive Guide
            

            

              
SQL JOIN: A Comprehensive Guide

              
anaell

            

          

        
      


     

      

        
          

            

              SQL Joins in ASP.Net 2.0 with Visual Studio 2005
            

            

              
SQL Joins in ASP.Net 2.0 with Visual Studio 2005

              
aagamri

            

          

        
      


     

      

        
          

            

              Optimizing Join Enumeration in Transformation-based Query Optimizers
            

            

              
Optimizing Join Enumeration in Transformation-based Query Optimizers

              
vier784

            

          

        
      


     

      

        
          

            

              SQL Outer Join in Database Queries
            

            

              
SQL Outer Join in Database Queries

              
bcred

            

          

        
      


     

      

        
          

            

              Overview of Different Types of SQL Joins
            

            

              
Overview of Different Types of SQL Joins

              
zhon_yk

            

          

        
      


     

      

        
          

            

              Modern Performance in SQL Server
            

            

              
Modern Performance in SQL Server

              
les_la

            

          

        
      


     

      

        
          

            

              Unorthodox SQL Techniques Unleashed: REVENGE: The SQL
            

            

              
Unorthodox SQL Techniques Unleashed: REVENGE: The SQL

              
ryng791

            

          

        
      


     

      

        
          

            

              SQL Server Polybase: Data Virtualization Overview
            

            

              
SQL Server Polybase: Data Virtualization Overview

              
morrell_c

            

          

        
      


     

      

        
          

            

              Overview of BlinkDB: Query Optimization for Very Large Data
            

            

              
Overview of BlinkDB: Query Optimization for Very Large Data

              
akush

            

          

        
      


     

      

        
          

            

              Comprehensive Overview of SQL: Commands and Categories
            

            

              
Comprehensive Overview of SQL: Commands and Categories

              
tika_3

            

          

        
      


     

      

        
          

            

              PL/SQL Benefits and Objectives
            

            

              
PL/SQL Benefits and Objectives

              
omar_c

            

          

        
      


     							

						

					

					

				


				

			

		
		

			

				
				

					
More Related Content

					

						

														

					

				

				
			

		












 





  
  
  



giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#