Menu
I have a large table User and a small table User_purchase in google bigquery.
If I join the two with
SELECT User.id, User_purchase.amount FROM User
LEFT JOIN User_purchase on User.id = User_purchase.user_id,
the query returns error:
Query Failed. Error: Not Implemented: This table cannot be read
But if I join the two with
SELECT User.id, ISNULL(INTEGER(User_purchase.amount), INTEGER(0)) FROM User
LEFT JOIN User_purchase on User.id = User_purchase.user_id,
the query works.
Don't quite understand why the first case does not work. I assume in the first case I can get all users with their purchase_amount though some users will have NULL as their purchase_amount. Thanks.
346
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
Although the ISNULL function is handy, if you are doing large joins having this function in the join clause will slow down the query. A lot of this will also depend on the indexes you have setup and how the indexes are used for the join.
Null values in tables or views being joined never match each other. Since bit columns do not permit null values, a value of 0 appears in an outer join when there is no match for a bit column in the inner table. The result of a join of null with any other value is null.
The LEFT OUTER JOIN will return all rows from the left table, both where rows exist in the related table and where they does not. The WHERE NOT EXISTS() subquery will only return rows where the relationship is not met.
This is a bug relating to nested field names in query replies. I've got a fix for the bug but it won't go out until next week's release. Thanks for bringing it to our attention.
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
