Inner joins

Enrich order lines with names and categories from the product catalog.

Step 1 of 6 · Learn

Match catalog keys

selected_products is a filtered view of the existing products table, not a replacement catalog. The example includes IDs up to 203. An inner join retains only lines with a match. The catalog has one row per product_id, so each matched line appears once; a key-name join keeps one product_id column.

Lesson reference: PySpark and Scala

Match catalog keys

PySpark example

from pyspark.sql.functions import col, count, sum, coalesce, lit, concat

selected_products = products.filter(col("product_id") <= 203)

result = order_items.join(selected_products, "product_id", "inner").select("order_item_id", "product_id", "product_name").orderBy("order_item_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val selected_products = products.filter(col("product_id") <= 203)

val result = order_items.join(selected_products, Seq("product_id"), "inner").select("order_item_id", "product_id", "product_name").orderBy("order_item_id")
result.show()

Choose joined fields

The join combines line values from order_items with product names from the catalog. Select only the fields the report needs. Repeated purchases of a product are legitimate separate lines, not duplicate catalog records. If non-key names overlap, qualify them with DataFrame aliases.

PySpark example

from pyspark.sql.functions import col, count, sum, coalesce, lit, concat

selected_products = products.filter(col("product_id") <= 203)

result = order_items.join(selected_products, "product_id", "inner").select("order_item_id", "product_name", "line_total").orderBy("order_item_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val selected_products = products.filter(col("product_id") <= 203)

val result = order_items.join(selected_products, Seq("product_id"), "inner").select("order_item_id", "product_name", "line_total").orderBy("order_item_id")
result.show()

Filter matched lines

A category filter after the join uses a field from the catalog. The example keeps Electronics matches within the selected subset. A filter cannot restore lines whose products were excluded before the join; those matches never existed.

PySpark example

from pyspark.sql.functions import col, count, sum, coalesce, lit, concat

selected_products = products.filter(col("product_id") <= 203)

result = order_items.join(selected_products, "product_id", "inner").filter(col("category") == "Electronics").select("order_item_id", "product_name", "line_total", "category").orderBy("order_item_id")
result.show()

Scala example

import org.apache.spark.sql.functions._

val selected_products = products.filter(col("product_id") <= 203)

val result = order_items.join(selected_products, Seq("product_id"), "inner").filter(col("category") === "Electronics").select("order_item_id", "product_name", "line_total", "category").orderBy("order_item_id")
result.show()

example.pyPySpark

from pyspark.sql.functions import col, count, sum, coalesce, lit, concat selected_products = products.filter(col("product_id") <= 203) result = order_items.join(selected_products, "product_id", "inner").select("order_item_id", "product_id", "product_name").orderBy("order_item_id") result.show()

1. Read order linesNine lines reference six catalog products.

2. Match catalog keysMatch each line to one selected catalog row.

3. Inspect the outputUnmatched lines are absent; repeated purchases remain separate lines.

Source order_items9 rows

order_item_id	order_id	product_id	quantity	line_total
1	1001	201	1	59.5
2	1001	202	1	30
3	1002	203	2	99
4	1002	206	1	50
5	1003	204	1	35
6	1004	203	3	149.97
7	1004	205	1	70.02
8	1005	203	1	49.99
9	1006	201	2	120

Second input selected_products3 rows

product_id	product_name	category
201	Wireless keyboard	Electronics
202	Laptop stand	Office
203	Desk lamp	Home

Match catalog keys

Result6 rows

order_item_id	product_id	product_name
1	201	Wireless keyboard
2	202	Laptop stand
3	203	Desk lamp
6	203	Desk lamp
8	203	Desk lamp
9	201	Wireless keyboard

Compare the source columns with the transformed result.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.