Web shop join: cook the soup, count the baskets
About this app
Joins and groups on the practice database prodat: tools join products first forms all 9 combinations, on boils them down to 3 (with ambiguous column name and comma + where). customers × orders gives 23 · 184 = 4232 rows, 184 after on. group by order_id turns them into 80 baskets, sum(quantity) counts the items, having keeps 45. Plus: an order as a line or as a basket, monster table versus separate tables, deletion and foreign keys.
With explain mode: an AI tutor walks you through the app, points out what matters in the scene and answers your questions.
Join = cross product + condition
A JOIN connects two tables. Logically it first combines every row of one table with every row of the other: 3 and 3 rows become 9, 23 customers and 184 order lines become 4232 combinations. The ON condition keeps only the pairs that belong together, such as equal customer numbers. If a column name exists in both tables, the table name goes in front (customers.customer_id); otherwise the database reports ambiguous column name. FROM a, b with WHERE gives the same result as JOIN … ON.
What the app shows
Six scenes on the practice database prodat: the cross product tools × products boiled down to three rows; the four tables customers, orders, products and deliveries with their keys; customers × orders as a grid in which exactly one customer per order line remains after ON; GROUP BY order_id turning 184 order lines into 80 baskets, with sum(quantity) and HAVING; two readings of “order” (25 lines versus 45 baskets); and a monster table versus separate tables with deletion and a foreign key.
Try it yourself
Pick a scene and go through the steps: write the condition without table names first, boil the soup down, group the order lines, set the threshold of five items and delete a customer. The numbers in the panel show how many rows, baskets or references are left at each point.
Frequently asked questions
- What does an SQL JOIN without a condition do?
- It forms the Cartesian product: every row of the first table with every row of the second. Two tables with n and m rows give n · m rows. Only the ON condition (or WHERE) keeps the pairs that belong together.
- What does ambiguous column name mean?
- A column with this name exists in several tables of the query, such as id in tools and in products. The database does not know which one is meant. The fix is to write the table name in front, for example tools.id.
- When HAVING, when WHERE after a join?
- WHERE checks single rows, HAVING checks groups after GROUP BY. “Baskets with more than five items” needs GROUP BY order_id and HAVING sum(quantity) > 5; WHERE quantity > 5 instead checks every order line on its own.
- What can I try in the app?
- Six scenes with steps: cross product and ON, the tables of the web shop, customers × orders, baskets with GROUP BY and HAVING, line versus basket and deletion with a foreign key. An explanation mode guides you through everything.
Subject: Computer science
More from Computer science
- Ackermann function
- Elevator simulator
- Binary workshop
- Binary versus linear search
- The Collatz problem
- Inside a CPU
- Swap the inside
- One frame in 16 milliseconds — how a game engine renders an image (3D)
A VisuApp by heyprof: interactive, free in your browser, no sign-up. What is a VisuApp? · All apps