6 ms·

This is such a pervasive issue with the relational SQL query model ! The fact that joins always create brand new tables is powerful, but it's rarely what is nee

This is such a pervasive issue with the relational SQL query model ! The fact that joins always create brand new tables is powerful, but it's rarely what is needed. The example in this article can manage to do without a join because it's only using the other tables to filter the customer table. The proposed solution breaks down as soon as a column needs to be brought in from the other tables (for example, "the total number of products purchased from the Beauty category"). A notion as simple as "I want to return a list of customers with additional properties" does not map to any dedicated SQL construct ! You can JOIN then GROUP BY, but making sure that the result only has exactly one value per customer is not something you can explicitly say in your SQL, it's a consequence of how you set up your JOIN and GROUP BY keys, and it's both easy to make a mistake and hard to understand the intent.

croes 3y ago
You could CROSS APPLY or OUTER APPLY with the Top (1) of a subquery to get exactly one value per customer per row.
- hikarikuen 3y ago
  [dead]
deleted 3y ago
[deleted]
dragonwriter 3y ago
> A notion as simple as "I want to return a list of customers with additional properties" does not map to any dedicated SQL construct ! You can JOIN then GROUP BY, but making sure that the result only has exactly one value per customer is not something you can explicitly say in your SQL. You can, you say it by grouping on only columns from the customers table, and those columns including a candidate key. Necessarily and equivalently, this means everything not from the customers table must be only referenced in an aggregate expression, but that's trivially what “I want a single row per customer, with some data not from the customers table” is asking. > it's a consequence of how you set up your JOIN and GROUP BY keys, Well, its a consequence of the GROUP BY. which is literally “what do you want one row for each of”, so... it's kind of weird to complain that it should be something else. GROUP BY is the dedicated construct in SQL that soecifies the thing you are looking for.
- victorNicollet 3y ago
  I've gone into more detail about this a few years ago in https://nicollet.net/blog/vanishing-schema-paradox.html https://nicollet.net/blog/vanishing-schema-paradox.html but here's a short summary. Suppose you have the following analytics query: SELECT Customer.Name, sum(Sales.Quantity) FROM Customer INNER JOIN Sales ON Customer.A = Sales.A GROUP BY Customer.B Can you tell whether this will return exactly one line for each customer ? If INNER JOIN + GROUP BY was the dedicated construct to do so, then the answer would be "yes", because by definition it is the dedicated construct to do so. That's what a dedicated construct does: it is dedicated to doing that thing. But both INNER JOIN and GROUP BY are much more versatile than that. In order to return exactly one line per customer, the following must be true: 1. column Customers.B must be a unique key of the Customers table (otherwise you'll get duplicate lines), and 2. each value of column Customer.A must also appear in column Sales.A (otherwise you'll have missing lines). Neither of these properties can be ascertained by looking at the query alone. A dedicated construct would be something like (imaginary syntax): SELECT Customer.Name, sum(Sales.Quantity) FROM Customer INNER JOIN Sales ON Customer.A = Sales.A GROUP INTO Customer
  - RHSeeger 3y ago
    As I read what you wrote, my first thought is that you don't have any such thing as a "customer" in that data model. You can ask for "one line for each customer.B" (which is what you're doing). But the idea that you can ask for "one line per customer" relies on some amount of non-db, domain knowledge. If you can't define what a "customer" is via the information in your database alone, then you can't query based on "a customer". And if the answer is "each row in Customer with a unique B", then that's part of the definition and reasonable to use as knowledge in getting "one row per customer". I didn't explain that well, I think... but that's the general thought that was running through my head as I read your writing.
    - victorNicollet 3y ago
      I agree ! This creates a situation where the SQL query does not represent the domain knowledge, but makes assumptions about it and cannot be understood without it. And while this will always be true for the more unusual parts of the domain, it is quite disappointing to be unable to properly represent as simple a concept as "what is a customer?" in SQL. It's the same as a language forcing you to use `c & ~0x20` because it doesn't have a `Char.toUpper(c)` function. The code works (under the right assumptions) and produces the same result, but it does not convey the concept of converting a letter to uppercase. What makes it so frustrating is that the DDL portion of SQL spends significant effort on representing such concepts in the database schema ! I can create a Customers table, with its primary key, and its foreign keys into and from other tables, and so on. I can represent "these are all the customers" in DDL, I can represent "every sale must be associated to a customer", and so on. But after the first join, I'm no longer using the Customers table, I'm using a new in-memory relation with no primary or foreign keys, and the concept that "this is the customers table, but with extra fields" is something I need to keep track of in my head, instead of in the language.
arichard123 3y ago
I think you want a lateral join.
- nice-try-fletch 3y ago
  Well, SQL Server (seemingly the author's area of expertise) doesn't have a lateral join. You can simulate this to some extent using CROSS APPLY, but that wouldn't really be any better than EXISTS in the case like the example, where you don't actually need any data from the secondary table.
nice-try-fletch 3y ago
But the example is exactly the type of query where I often see DISTINCT used where it shouldn't: they just want to know the customer with at least one order or the author who wrote at least one book or the user who upvoted at least one comment. I think that was the point of the post (not to never use DISTINCT or that this is the only example where anyone ever needs to join). DISTINCT isn't likely to be used in the more elaborate example you mention (total products from some category) because DISTINCT doesn't fix a problem that the necessary GROUP BY to get the answer has already prevented appearing anyway.
- sgarland 3y ago
  If you don’t need the result from the joined table, a semijoin is often the fastest way. In the given example, if there was more data in the tables, it’s likely the planner would choose that. Not as familiar with MSSQL as MySQL and Postgres so I’m not positive. Also, the good news is the planners are increasingly better at converting your desired end state into queries utilizing features like this, without you explicitly telling them to.
mattxxx 3y ago
Agreed - I think SQL is great, but the JOIN operator has been problematic for me with analytic queries and queries where you want to enrich data across different tables. I think the problem is fundamentally that if you take a record, and split it across 2 tables, then there's no easy operation to merge them and guarantee distinctness. Separately, even though there are some %-breakdowns in the article, I don't think the `DISTINCT` sort is so much of a problem in most cases. Usually the actually joining is the problem for any truly large dataset, so while I trust the diagrams shown, I think those %'s change dramatically based on the underlying data.
- marcosdumay 3y ago
  > I don't think the `DISTINCT` sort is so much of a problem in most cases I train people new to SQL at work, and I can tell you with complete certainty that you only get this impression because people learn very quickly to never use `distinct`. It's by far the most common cause of performance problems for people new to SQL.
- sgarland 3y ago
  Imagine you’re a SaaS company with millions of customers. Each of those customers will have tens or hundreds of recorded payments (orders). Even if you scale down on customers by two orders of magnitude, the intermediate table starts growing rapidly.
freddotclem 3y ago
> A notion as simple as "I want to return a list of customers with additional properties" does not map to any dedicated SQL construct. a temp table with unique/primary key on custumer really helps if uniqueness is a requirement.

croes 3y ago

You could CROSS APPLY or OUTER APPLY with the Top (1) of a subquery to get exactly one value per customer per row.

hikarikuen 3y ago
[dead]

deleted 3y ago

[deleted]

dragonwriter 3y ago

> A notion as simple as "I want to return a list of customers with additional properties" does not map to any dedicated SQL construct ! You can JOIN then GROUP BY, but making sure that the result only has exactly one value per customer is not something you can explicitly say in your SQL. You can, you say it by grouping on only columns from the customers table, and those columns including a candidate key. Necessarily and equivalently, this means everything not from the customers table must be only referenced in an aggregate expression, but that's trivially what “I want a single row per customer, with some data not from the customers table” is asking. > it's a consequence of how you set up your JOIN and GROUP BY keys, Well, its a consequence of the GROUP BY. which is literally “what do you want one row for each of”, so... it's kind of weird to complain that it should be something else. GROUP BY is the dedicated construct in SQL that soecifies the thing you are looking for.

victorNicollet 3y ago
I've gone into more detail about this a few years ago in https://nicollet.net/blog/vanishing-schema-paradox.html https://nicollet.net/blog/vanishing-schema-paradox.html but here's a short summary. Suppose you have the following analytics query: SELECT Customer.Name, sum(Sales.Quantity) FROM Customer INNER JOIN Sales ON Customer.A = Sales.A GROUP BY Customer.B Can you tell whether this will return exactly one line for each customer ? If INNER JOIN + GROUP BY was the dedicated construct to do so, then the answer would be "yes", because by definition it is the dedicated construct to do so. That's what a dedicated construct does: it is dedicated to doing that thing. But both INNER JOIN and GROUP BY are much more versatile than that. In order to return exactly one line per customer, the following must be true: 1. column Customers.B must be a unique key of the Customers table (otherwise you'll get duplicate lines), and 2. each value of column Customer.A must also appear in column Sales.A (otherwise you'll have missing lines). Neither of these properties can be ascertained by looking at the query alone. A dedicated construct would be something like (imaginary syntax): SELECT Customer.Name, sum(Sales.Quantity) FROM Customer INNER JOIN Sales ON Customer.A = Sales.A GROUP INTO Customer
- RHSeeger 3y ago
  As I read what you wrote, my first thought is that you don't have any such thing as a "customer" in that data model. You can ask for "one line for each customer.B" (which is what you're doing). But the idea that you can ask for "one line per customer" relies on some amount of non-db, domain knowledge. If you can't define what a "customer" is via the information in your database alone, then you can't query based on "a customer". And if the answer is "each row in Customer with a unique B", then that's part of the definition and reasonable to use as knowledge in getting "one row per customer". I didn't explain that well, I think... but that's the general thought that was running through my head as I read your writing.
  - victorNicollet 3y ago
    I agree ! This creates a situation where the SQL query does not represent the domain knowledge, but makes assumptions about it and cannot be understood without it. And while this will always be true for the more unusual parts of the domain, it is quite disappointing to be unable to properly represent as simple a concept as "what is a customer?" in SQL. It's the same as a language forcing you to use `c & ~0x20` because it doesn't have a `Char.toUpper(c)` function. The code works (under the right assumptions) and produces the same result, but it does not convey the concept of converting a letter to uppercase. What makes it so frustrating is that the DDL portion of SQL spends significant effort on representing such concepts in the database schema ! I can create a Customers table, with its primary key, and its foreign keys into and from other tables, and so on. I can represent "these are all the customers" in DDL, I can represent "every sale must be associated to a customer", and so on. But after the first join, I'm no longer using the Customers table, I'm using a new in-memory relation with no primary or foreign keys, and the concept that "this is the customers table, but with extra fields" is something I need to keep track of in my head, instead of in the language.

arichard123 3y ago

I think you want a lateral join.

nice-try-fletch 3y ago
Well, SQL Server (seemingly the author's area of expertise) doesn't have a lateral join. You can simulate this to some extent using CROSS APPLY, but that wouldn't really be any better than EXISTS in the case like the example, where you don't actually need any data from the secondary table.

nice-try-fletch 3y ago

But the example is exactly the type of query where I often see DISTINCT used where it shouldn't: they just want to know the customer with at least one order or the author who wrote at least one book or the user who upvoted at least one comment. I think that was the point of the post (not to never use DISTINCT or that this is the only example where anyone ever needs to join). DISTINCT isn't likely to be used in the more elaborate example you mention (total products from some category) because DISTINCT doesn't fix a problem that the necessary GROUP BY to get the answer has already prevented appearing anyway.

sgarland 3y ago
If you don’t need the result from the joined table, a semijoin is often the fastest way. In the given example, if there was more data in the tables, it’s likely the planner would choose that. Not as familiar with MSSQL as MySQL and Postgres so I’m not positive. Also, the good news is the planners are increasingly better at converting your desired end state into queries utilizing features like this, without you explicitly telling them to.

mattxxx 3y ago

Agreed - I think SQL is great, but the JOIN operator has been problematic for me with analytic queries and queries where you want to enrich data across different tables. I think the problem is fundamentally that if you take a record, and split it across 2 tables, then there's no easy operation to merge them and guarantee distinctness. Separately, even though there are some %-breakdowns in the article, I don't think the `DISTINCT` sort is so much of a problem in most cases. Usually the actually joining is the problem for any truly large dataset, so while I trust the diagrams shown, I think those %'s change dramatically based on the underlying data.

marcosdumay 3y ago
> I don't think the `DISTINCT` sort is so much of a problem in most cases I train people new to SQL at work, and I can tell you with complete certainty that you only get this impression because people learn very quickly to never use `distinct`. It's by far the most common cause of performance problems for people new to SQL.
sgarland 3y ago
Imagine you’re a SaaS company with millions of customers. Each of those customers will have tens or hundreds of recorded payments (orders). Even if you scale down on customers by two orders of magnitude, the intermediate table starts growing rapidly.

freddotclem 3y ago

> A notion as simple as "I want to return a list of customers with additional properties" does not map to any dedicated SQL construct. a temp table with unique/primary key on custumer really helps if uniqueness is a requirement.