Overview
I am trying to run a query to join some data in 3 tables in BigQuery (should apply to most relation databases like PostgreSQL as well) using SQLAlchemy Core.
Input Data
Table 1 (t1)
| c1 | c2 | x |
|---|---|---|
| 0 | 0 | 0 |
| 0 | 1 | 1 |
| 1 | 0 | 2 |
Table 2 (t2)
| c1 | c2 | y |
|---|---|---|
| 0 | 0 | 3 |
| 1 | 1 | 4 |
| 1 | 2 | 5 |
Table 3 (t3)
| c1 | c2 | z |
|---|---|---|
| 0 | 0 | 6 |
| 1 | 1 | 7 |
| 2 | 2 | 8 |
Desired Query
The query I am trying to run is
SELECT
*
FROM
t1
FULL OUTER JOIN
t2
USING (c1, c2)
FULL OUTER JOIN
t3
USING (c1, c2)
which yields (- indicates null):
| c1 | c2 | x | y | z |
|---|---|---|---|---|
| 0 | 0 | 0 | 3 | 6 |
| 0 | 1 | 1 | - | - |
| 1 | 0 | 2 | - | - |
| 1 | 1 | - | 4 | 7 |
| 1 | 2 | - | 5 | - |
| 2 | 2 | - | - | 8 |
Problem
The problem I am running into is that it does not seem possible to perform joins with USING in SQLAlchemy Core. In particular, the join method only supports an onclause and not USING.
The two solutions I am attempting are:
- Try to get
USINGto work with aTextClause. This is proving difficult as I need to consume the resulting table, referring to the columns by name. - Try to do the joins with the provided SQLAlchemy join method. I am unable to replicate this query using
JOINs withONwithout making subqueries.
Any help would be appreciated. Thanks!