How many joins

To match the key, every record in orders has a product ID in the orders. If we want to combine information about an order with information about the product that was ordered, we can do an inner join :. Even if we use the correct fields, there is a trap here for the unwary. If we look at the reviews table, it has 1, entries:. We can then combine the products and reviews table and count the number of distinct products in the result. Only of the products have any reviews.

This query demonstrates the problem:. This kind of join always returns at least one record from the first table we mention i.

To see how it works, imagine we have two little tables called paint and fabric. The paint table contains three rows:. If we do an inner join on these two tables, matching paint. CustomerName, Orders. Exercise: Insert the missing parts in the JOIN clause to join the two tables Orders and Customers , using the CustomerID field in both tables as the relationship between the two tables.

Report Error. Your message has been sent to W3Schools. W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. We have turned the one-to-many relationship into a one-to-one relationship by doing multiple levels of aggregation using a subquery. Subqueries have a bad reputation among analysts because older database systems execute them using inefficient recursive evaluation, but any modern data warehouse will generate an efficient plan from the above query.

As a bonus, we've avoided the use of more costly distinct aggregates, and there's no possibility of a "combinatorial explosion" producing huge intermediate results. To my knowledge, no BI tool currently uses this technique, but if you're writing SQL queries by hand, this is a useful trick. Support Log in. Centralize your data within a target destination with fully managed data connectors. By use case. Customer Support. Data Engineering. Database Replication.

Find a Partner. Support Portal.