Joining Data From Different Tables

It’s a common pattern for developers to use the results from one table as the input or filter for another table. You’ll often see queries like:

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders)
-- or
SELECT * FROM customers JOIN orders ON (customer_id)

These queries only return rows from the customers table that have matching rows in the orders table based on customer_id.

SQL databases, including DuckDB, generally assume that all data stored in tables can be scanned independently, without requiring any input parameters. Consequently, when a developer performs a join between two tables, DuckDB will scan each table independently: first scanning one table, then the other with values from the first.

But don’t lose hope—there are ways to work around this constraint!

Example: Listing S3 Bucket Sizes

Let’s walk through an example: You want to find out how many keys are stored in each S3 bucket of an AWS account, as well as the total size of those keys.

List all S3 Buckets

First, we retrieve the list of S3 buckets along with their associated AWS Profile.

 SELECT name, _aws_profile FROM (
   SELECT
     UNNEST(buckets, max_depth := 2),
     _aws_profile
   FROM aws.s3.list_buckets
 )

Combine Bucket List with Key List

Next, we attempt to list the keys stored in each S3 bucket by combining the aws.s3.list_buckets and aws.s3.list_objects_v2 tables.
```
 WITH all_buckets as (
   SELECT
   UNNEST(buckets, max_depth := 2),
   _aws_profile
   FROM aws.s3.list_buckets
 ),
 SELECT * FROM aws.s3.list_objects_v2, all_buckets WHERE
 bucket = all_buckets.bucket_name and
 _aws_profile = all_buckets.profile
 limit 10
```
This query will fail because DuckDB scans tables independently and in parallel. It doesn’t know to pass the output of the all_buckets table as clauses in the WHERE section of the query.
```
IO Error: Airport @ grpc://localhost:8080/aws/s3/list_objects_v2:
Missing values for input field "bucket".. Detail: Failed.
```
The problem arises because aws.s3.list_objects_v2 requires a bucket input parameter to know which bucket to list. However, DuckDB assumes the table can return data without needing any input parameters.

Use a DuckDB Variable.

To resolve this, we can collect the S3 bucket names and AWS profiles into a DuckDB variable, which can then be passed as an input parameter to the aws.s3.list_objects_v2 table.

  SET VARIABLE bucket_names_and_profiles = (
     SELECT
     LIST({'name': name, 'profile': _aws_profile})
     FROM (
      SELECT
        UNNEST(buckets, max_depth := 2),
        _aws_profile
        FROM aws.s3.list_buckets
      )
  )

This approach ensures DuckDB uses the variable’s value as an input parameter not as a join.

Produce the Final Results

Now, you can use the variable to produce the final output:

SELECT bucket, count(*), sum(size)
FROM (
  SELECT unnest(contents, max_depth := 2), bucket
  FROM aws.s3.list_objects_v2
  WHERE {'name': bucket, 'profile': _aws_profile} IN getvariable('bucket_names_and_profiles')
)
GROUP BY bucket;