10.4 JOIN ON

The explicit SQL JOIN ON syntax is currently considered as the proper way to write SQL join operations,

10.4.1 Explicit join by the keywords JOIN and ON

select *
from  sku_data s JOIN order_item o ON s.sku = o.sku 
;
TABLE10.3: 7 records
SKU SKU_Description Department Buyer OrderNumber SKU Quantity Price ExtendedPrice
100200 Std. Scuba Tank, Magenta Water Sports Pete Hansen 3000 100200 1 300 300
101100 Dive Mask, Small Clear Water Sports Nancy Meyers 2000 101100 4 50 200
101100 Dive Mask, Small Clear Water Sports Nancy Meyers 3000 101100 2 50 100
101200 Dive Mask, Med Clear Water Sports Nancy Meyers 2000 101200 2 50 100
101200 Dive Mask, Med Clear Water Sports Nancy Meyers 3000 101200 1 50 50
201000 Half-dome Tent Camping Cindy Lo 1000 201000 1 300 300
202000 Half-dome Tent Vestibule Camping Cindy Lo 1000 202000 1 130 130

10.4.2 Combine JOIN ON and WHERE

The following statement

select *
from  sku_data s JOIN order_item o ON s.sku = o.sku 
where s.department='Water Sports'
;
TABLE10.4: 5 records
SKU SKU_Description Department Buyer OrderNumber SKU Quantity Price ExtendedPrice
100200 Std. Scuba Tank, Magenta Water Sports Pete Hansen 3000 100200 1 300 300
101100 Dive Mask, Small Clear Water Sports Nancy Meyers 2000 101100 4 50 200
101100 Dive Mask, Small Clear Water Sports Nancy Meyers 3000 101100 2 50 100
101200 Dive Mask, Med Clear Water Sports Nancy Meyers 2000 101200 2 50 100
101200 Dive Mask, Med Clear Water Sports Nancy Meyers 3000 101200 1 50 50

can be rewritten to:

select *
from  sku_data s JOIN order_item o ON s.sku = o.sku AND s.department='Water Sports'
;
TABLE10.5: 5 records
SKU SKU_Description Department Buyer OrderNumber SKU Quantity Price ExtendedPrice
100200 Std. Scuba Tank, Magenta Water Sports Pete Hansen 3000 100200 1 300 300
101100 Dive Mask, Small Clear Water Sports Nancy Meyers 2000 101100 4 50 200
101100 Dive Mask, Small Clear Water Sports Nancy Meyers 3000 101100 2 50 100
101200 Dive Mask, Med Clear Water Sports Nancy Meyers 2000 101200 2 50 100
101200 Dive Mask, Med Clear Water Sports Nancy Meyers 3000 101200 1 50 50

10.4.3 Example

Query: What is the total sales of the department ‘Water Sports’?

/*  */
select s.department, sum(o.extendedprice) as total_sales
from  sku_data s JOIN order_item o 
      ON s.sku = o.sku and s.department = 'Water Sports'
;
TABLE10.6: 1 records
department total_sales
Water Sports 750

10.4.4 Workout

  • Retrieve total sales for the department ‘Water Sports’

  • Retrieve total sales for the department ‘Climbing’

  • Retrieve total sales for the department ‘Camping’

  • Retrieve total sales for each department

select s.department, sum(o.extendedprice) as total_sales
from  sku_data s JOIN order_item o on s.sku = o.sku 
group by s.department
;
TABLE10.7: 2 records
department total_sales
Camping 430
Water Sports 750