9 ms·
Don't use DISTINCT as a "join-fixer"
- tuukkah 3y agoNext question: Why doesn't the query optimizer make this change automatically? The version with DISTINCT is much nicer to write and read.
- chaps 3y agoYeah, using `SELECT 1` language is a big nah from me. I want my queries to be readable by both new and experienced.
- gigatexal 3y agoThis isn’t readable? It’s a common pattern.
- funnymony 3y agoIts a matter of your eyes being used to this. If for ten years you always indented the code this way Void F() { Foo(); Bar(); Baz();} Then following snippet will seem hard to parse mentally: Void F() { Foo(); Bar(); Baz(); } And vice versa
- wvenable 3y agoI assume you could just use `SELECT *` there and the result is the same.
- TurkTurkleton 3y agoIndeed, I would be surprised if there exists any RDBMS where the content of the select list in an EXISTS subquery matters. Postgres's SQL dialect even lets you use an empty select list (`... WHERE EXISTS (SELECT FROM related_table WHERE related_id = id_from_outer_query)`). In T-SQL, however, a non-empty select list is required, and in my experience, developers writing T-SQL tend to prefer a constant value like `1` or `NULL` over `*` -- I suspect there's some superstition there related to both the common wisdom that `SELECT *` queries are to be avoided (which is true -- you really should only ever select the columns of interest) and a lack of truly understanding that EXISTS only reads enough of the table to ascertain whether rows exist that satisfy the subquery's predicate, and returns a boolean value, not a result set.
- gpm 3y agoAs a non-sql-expert (I've used an exists query approximately once in my life) I prefer `select 1` over `select column_name` or `select *` because it doesn't look like it might be meaningful. Postgres's dialect seems like it made the right choice here.
- jrumbut 3y agoSelecting NULL to signify something exists breaks my brain a little bit. I really prefer 1 stylistically, even if there is no technical difference.
- slotrans 3y agowhere exists (select 1...) is completely standard and normal. We should write our code to be readable, but it is reasonable to expect that the reader knows the language.
- chaps 3y agoHas nothing to do with the language. 1 (in its isolated presentation) exists outside of my set of data and that's not something that gives me comfortable feelings. It forces me to think (less think more good) why some random value needs to float around my data and maybe needs to be appended or summed or whatever.. because reasons. As far as I'm concerned you might as well make it 200 or 1648194. And why would those ever be needed in any context around my data?
- naikrovek 3y ago1 isn't data, here. it's just a flag to return that row. I am sure you know this, so I'm wondering why you consider it data which is outside of your data set. it isn't data when used like this.
- chaps 3y agoIn the sense that code is data: yes, it absolutely is data.
- naikrovek 3y agodon't be obtuse. it isn't in the dataset and it isn't returned in the results. > code is data, so it's data dude, don't. this is the weakest and most grasping argument I think I've ever heard.
- chaps 3y agoNo need to be a dismissive dickhole about it, friend. Maybe I'm not wording it right then? Like I said in other posts, I copy paste SQL statements all the time. If I were to copy/paste that statement then all I'd get is a bunch of ones and that's useless to me. The SQL itself is data to me in the same way that when I view a lazy list comprehension, I view it (and SQL) as something that's just waiting to be run. Maybe not now, and maybe not in the current SQL, but there's a non-zero chance that I'll copy/paste it. So in that context, a bunch of ones is useless to me and IS data because the ones are literally the output of the SQL statement. Better to generalize my code writing process in a way so that I can copy/paste a "select *" or "select rownum" because those are more useful down the line. Really think we all just code differently.
- mvdtnz 3y agoStrange hill to die on - the SELECT 1 query is not particularly hard to understand, and it's demonstrably better.
- chaps 3y agoEh, anytime I need to read SQL that includes that I have to sit down and think about it. It's not glance-and-understand and you have to maintain that state in your head. Not the sort of SQL I want to rely on as a practice. Mind you, most of what I write are ad hoc SQL queries where I might write 100-200 queries in a single day. So that sort of thing really adds up. And it's in a strange place where performance isn't dire, since I can work on other stuff while queries chug.
- wodenokoto 3y agoHelp a fellow out here. What’s the purpose?
- noisem4ker 3y agoWith most databases (PostgreSQL is an exception), SELECT must select something, even if you only care about the record existing in general, and you're not interested in reading anything from it. SELECT 1 means "select the first column", whatever it is. Specifying a column number also works with ORDER BY and other clauses. Of course any natural number works, as long as the record has at least that many fields.
- pcthrowaway 3y agoI'm pretty sure you still have to select something in postgres... or are you saying there's a different operator to do the same thing? > SELECT 1 means "select the first column" interesting.. in postgres this actually gives you a single-column value of 1, in an unnamed column... I haven't used other DBMSes much but expected similar behaviour
- wruza 3y agoSELECT 1 means "select the first column", whatever it is No, it selects a literal integer 1 in all SQL engines. You can, and do, select any expressions, not only column names. CategoryId, CategoryId + 1, 1, sqrt(CategoryId * ProductId), Column names alone are expressions too. SQL queries return not columns from table, but a result of relational operations with new columns specified by expressions. What a downvoted user itt cannot accept is that 1 is as meaningful as * or <colname> in general and is an idiom for “columns don’t matter here, only [the number of] rows”. It’s like [[1]].length vs [[id, …]].length. Saying “it’s not my data” is misunderstanding what that data is.
- nsenifty 3y agoYeah just change it to `SELECT OrderID`.
- chaps 3y agoYep, agreed.
- thaumasiotes 3y agoI don't get it. "SELECT 1" is significantly more informative than "SELECT red_herring". It's a self-documenting and automatically enforced comment telling you that the information requested by the SELECT statement is not used by the query.
- chaps 3y agoI copy paste SQL all the time, especially with CTE after CTE all the time. The row number is a simple thing to copy paste that fills multiple functions with zero modification.
- thaumasiotes 3y agoAnd that's different from 1 in what way other than being more confusing? Note that in the case you're advocating for, the row number is called "OrderId", which you might have trouble with if you insist on copying a query from somewhere else and using it without modification. Wouldn't you prefer "1"?
- chaps 3y agoIt's helpful within CTEs as a unique identifier. This whole conversation makes me wonder if not many people minmax this sort of meta programming thing.
- paulddraper 3y agoYou can write `SELECT NULL` or `SELECT *` if you prefer. PostgreSQL allows a zero-column `SELECT`.
- croes 3y agoThen write Select *
- inopinatus 3y agoIt’s literally the documented idiom for producing singular output from amplified rows at https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS https://www.postgresql.org/docs/current/functions-subquery.h....
- Sesse__ 3y agoIf you don't like EXISTS, the other standard way to get a semijoin is using IN with a subquery. SELECT * FROM a WHERE x IN (SELECT y FROM b);
- meinaccount00 3y agoI've been working with Spark lately and just beeing able to write "semi join" (or "anti join") is quite nice and readable
- Sesse__ 3y agoExplicit semi- and antijoin operators would be a nice addition to the language, but do note that they present some additional challenges for the optimizer. In particular, when the join condition becomes explicit, it can take on more forms than before, which can limit the reorderings allowed or allow new ones. E.g. you now need to deal with this structure: (a SEMIJOIN b ON a.x=b.y) JOIN c ON b.z=c.z This was an impossible structure before, since IN and EXISTS both hide b's columns (all semi- and antijoins effectively come last), and your optimizer will now need to know whether e.g. this associative rewrite is allowed or not: a SEMIJOIN (b JOIN c ON b.z=c.z) ON a.x=b.y Also, you'll need to deal with LATERAL semijoins, which you didn't before… None of this is impossible, but there's more to it than just a small syntactic change.
- magicalhippo 3y agoJust to be sure to check the resulting plans. The DB we use usually has sub-optimal plans when using IN compared to EXISTS, often quite drastically so.
- Sesse__ 3y agoYou always need to check the resulting plans if you care about execution performance, that's just how life is. I'm offhand a bit surprised IN does worse than EXISTS; I can understand NOT IN being slow, because it has very surprising NULL handling that is hard to optimize for.
- gigatexal 3y agoQuery optimizers are already black boxes of magic I guess one has to stop somewhere. A top level distinct to a query like this is a huge red flag. Even in a sub select or a join clause id rather have a group by than a distinct.
- Sesse__ 3y agoThere's no good reason why you couldn't rewrite DISTINCT to GROUP BY, but remember that it wouldn't work in all cases; you could have both. E.g. SELECT DISTINCT COUNT(*) FROM t1 GROUP BY a;. Similarly, HAVING and window functions happen after GROUP BY but before DISTINCT, so a simple move won't always be correct and you'd have to account for that.
- _a_a_a_ 3y ago> There's no good reason why you couldn't rewrite DISTINCT to GROUP BY Exactly. People post a lot of things about SQL optimisation without knowing anything about it. Frankly, I'm getting really fed up with it on HN.
- slotrans 3y agoThe optimizer doesn't know your semantic intent.
- tuukkah 3y agoDoes it need it to do its job?
- phanimahesh 3y agoYes. As pointed out above the intent is not always obvious in complex queries, and the optimiser is already a large black box.
- twoodfin 3y agoActually, I’d think using “ANY” would be the clearest translation of the semantic intent. If your query processor is even marginally competent, ANY & EXISTS are indeed optimized exactly the same way. A slightly smarter QP will perform as well for the equivalent IN condition, too.
- sgarland 3y agoTangentially related, how IN and ANY can produce wildly different results [0] due to type casting. I have personally seen this, and at least for my data, the fastest query / fewest buffers scanned was to shift the matching ids out to a CTE with VALUES, and join with IN. It wasn’t a minor speed up, either, it was literally twice as fast. [0]: https://dba.stackexchange.com/a/125500 https://dba.stackexchange.com/a/125500
- Diggsey 3y agoNobody actually answered your question helpfully, so here goes: When you write `SELECT DISCTINCT a, b` you are asking for every unique pair (a, b) in the result. Specifically, the same value of `a` could appear multiple times in the result paired with different values of `b`. The only way to implement that "generally" is to visit all of the rows in the result-set, and skip over duplicates using a hash table of "already seen" pairs, or by sorting the result. The problem is that this intermediate result-set is much bigger than we really want. As a human, we look at this and think "Oh, but `a` is the primary key (in this case CustomerID) and `b` is a column in the same table (CustomerName) so we only need to be distinct over `a`, which means the query optimizer should be able to "push down" the DISTINCT clause into the scan of the joined tables". The problem is that the query optimizer has not been endowed with this kind of knowledge about the specific relationship between `a` and `b`, and so cannot do this optimization. In principle, a query optimizer could implement this kind of optimization though. At this level you really start getting into the nitty gritty of what optimizations a particular database has implemented though: the solution presented in the article may be the best way to write this for SQL Server, but this "correlated subquery" form could be catastrophically slow in other database engines...
- marcosdumay 3y agoHum... If you know `a` is a key of table `X` and `b` is a key of table `Y`, you can replace a `select distinct` with a `join ... on exists ()` with complete certainty. And this solves almost all of the problematic cases of this on practice. If the thing you are querying isn't a key, you will naturally think a bit further and not write the distinct. I guess that kind of optimization just goes counter to the philosophy of SQL optimizers. I know that I would be deeply surprised if I found a database doing it, and not in a good way.
- Diggsey 3y agoYou're overthinking it - the optimization is absolutely possible as you've described. The reason databases haven't done is not some question of design philosophy, it's simply that nobody bothered to implement it. > I know that I would be deeply surprised if I found a database doing it, and not in a good way. SQL engines do way more surprising optimizations than this. SQL is a "4th gen" language after all, meaning it's absolutely within the spirit that the database engine does whatever it wants to get the result.
- cwillu 3y agoI'm pretty sure postgres will in fact do this in at least some cases.
- jletienne 3y agowhenever I see [select distinct ... group by] , i know i'm in for some bs. the original code creator had some duplication/cartesians but never properly addressed it
- morkalork 3y agoSeeing distinct being abused in application code is pretty rare though. What gets me is seeing it in some ETL or data analyst's queries. That's the sign that you're about to spend the next hour unfucking a query with like 50 joins and no CTEs for organization, written by someone who has no understanding of the underlying schema.
- thiht 3y agoI’ve done it a few times in the app I’m working with and had no idea DISTINCT was not the proper way to address it. I mean, it’s a standard feature, named « distinct », easy to use and it works. It’s on me that I didn’t run an explain on these queries, but it seems like an easy mistake to make. Anyway I’ll happily fix these in my code on Monday.
- sublinear 3y agoI know it's just an example, but why aren't these tables normalized better anyway? None of them should have columns for the PK of another table. The relationships between them should be represented by bridge tables. Really there's only one relationship here which is product orders. Why have the expense of a join in your select queries when you can just associate them properly during the insert in the first place? Then you can simply select limit 1 from the bridge table and only have one scan on the customer table for the whole query.
- thaumasiotes 3y ago> why aren't these tables normalized better anyway? None of them should have columns for the PK of another table. > The relationships between them should be represented by bridge tables. Really there's only one relationship here which is product orders. What? There appears to be a mistake in the Products table, which only allows a product to belong to one category. But the many-to-many relationship between orders and products is already represented in a bridge table, OrderDetails, and the one-to-many relationship between customers and orders is correct. Why would you have a bridge table between customers and orders instead of recording the same information in a column of the Orders table? And... how is your bridge table going to operate without having columns for the PKs of multiple other tables? That's what OrderDetails is doing. What would you change?
- icameron 3y agoGood points but what you think about Bob ordering 47.05 worth of Strawberries?
- thaumasiotes 3y agoNone of the products have any prices, so there's no such thing as ordering 47.05 worth of strawberries. The OrderDetails table already records how many strawberries were included in Bob's order of July 3rd.
- sitharus 3y agoMaybe Bob makes jam?
- deleted 3y ago[deleted]
- texuf 3y agoSo loop over the customers and see if they have an order instead of looping over all the orders and de-duplicating the customers?
- bowsamic 3y agoDon’t use subqueries if you are using Azure SQL, they’re painfully slow
- FridgeSeal 3y ago> Don’t use Azure SQL There, fixed it for you. /s
- continuational 3y agoQuite often what you want isn't an arbitrary row (for which exists is correct), but a specific one. In that case, PostgreSQL has a feature where you can do this: select distinct on (customer_id) customer_id, order_id from orders order by customer_id, created_date desc And get all customer IDs with their latest order ID.
- nightfly 3y agoIs that different/better/worse than? select customer_id, max(order_id) from orders group by customer_id order by customer_id
- hackernewds 3y agothe max order_id is not always the latest id although it would seem to be so by design, however design is only as robust as the thousands of individuals building on the system :)
- szundi 3y agoIf you have an auto-increment and DO NOT have some logic around draft orders, max could be the one. Either way, you can max on the submission date then
- eterm 3y agoMax on submission date doesn't return the corresponding order ID from that row, aggregations are applied separately. That's a classic pitfall that comes up all the time.
- withinboredom 3y agoSounds like a job for a windowing function.
- 3y ago
- thehours 3y ago> There was another interesting use case I wrote about a few years ago that showed how changing DISTINCT to GROUP BY – even though it carries the same semantics and produces the same results – can help SQL Server filter out duplicates earlier and have a serious impact on performance. I recently learned this is also what Amazon recommends when querying Spectrum. [1] [1] https://aws.amazon.com/blogs/big-data/10-best-practices-for-amazon-redshift-spectrum/ https://aws.amazon.com/blogs/big-data/10-best-practices-for-...
- aib 3y agoThis is ridiculous! Someone should fix it and send a p—oh.
- nubinetwork 3y agoHow do you get those fancy pictures displaying the explain output?
- 5e92cb50239222b 3y agoSQL Server Management Studio, I believe. For the rest of us who avoid Microsoft's database, there's this: https://explain.dalibo.com https://explain.dalibo.com https://explain.depesz.com https://explain.depesz.com
- nice-try-fletch 3y agoLooks like the Plan Explorer extension for Azure Data Studio.
- _8j50 3y agoSlightly unrelated question: Why does Lucene and derivatives (ES,Solr,etc..) not have the concept of a join? Always found it to be a major downside of them.
- victorNicollet 3y agoThis 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 agoYou 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.
- croes 3y agoMore than once a Distinct means you don't really know the underlying data model and just want to get rid of the duplicates.
- sakesun 3y agoGlad somebody said this.
- philshem 3y ago“Think, before you DISTINCT” (Not mine, and I don’t know the source)
- munch117 3y agoI don't. I put distinct on everything by default, because it makes SQL more like relational algebra. What's the argument for avoiding distinct? Is it performance? I've often wondered if the (sometimes redundant) distincts slow things down. Perhaps it does, but just not enough that I noticed.
- devin 3y agoThe article clearly explains, as do a number of comments here.
- munch117 3y agoIf it was that clear, then I wouldn't have asked.
- funnymony 3y agoPerformance implication exist, but it is secondary. Primary reason: distinct on every select shows either lack of knowledge of schema, in particular which columns make rows unique, or unfortunate schema design. (Apart from niche cases, schema should be somewhat normal. I.e. column parent_name belongs in the table parent, not in the table student) Select a from x where myuniquekey=1; —- guaranted to return 1 or zero rows, if myuiniquekey is actually unique. Select a from x join y on x.parent_id = y.y_id —- guaranteed to return same amount of rows as exist in y, never more, never duplicates y rows. (N-to-1 relation) If distinct is used in any of above, then question “why?” naturally arises. In more severe case, leads to bugs: Select distinct student.student_name, parent.parent_name from student join parent on student.parent_id = parent.parent_id —- silently discards rows, where by accident student/parent name combo matches several times. Technically sql allows comparing unrelated columns (colour=last_name), but for vast majority of cases, when joining, one of the side should be joined using it’s unique key, and other side should be using it’s foreign key, which ensures that duplicates don’t appear randomly, and thus distinct is not needed.
- denton-scratch 3y agoI lost interest once I came to the words "SQL Server", which I haven't used for a couple of decades. Unfortunately, those words didn't crop up until the last sentence of the article...
- dspillett 3y agoThere is no reason to mention SQL Server for most of the article, the issue being discussed is relevant to all (or at least all common) SQL implementations. The query pattern is flawed in a fundamental way, or the table structure necessitating it is, rather than this being a SQL Server specific trap. The only reason the product is specifically mentioned in that last part is because it mentions an optimisation that is possibly specific to SQL Server's query planner. If you have such an allergy to the product that you can't process general advice from someone who uses it, there are clues you could have triggered your anaphylaxis earlier and saved you time: t-sql is mentioned in the site's side-bars, as is “Learn SQL Server” for that matter.
- denton-scratch 3y agoI'm not allergic to SQL Server. I didn't study the example code; I was just curious about why DISTINCT was a problem. TL;DR: for some query planners, DISTINCT requires a table sort, which requires a full table scan, which can be avoided by using a construct (GROUP BY) that gives the planner hints. But I assumed that was dependent on the query planner; Oracle SQL, as I recall, has/had specific constructs for giving hints to the query planner.
- dspillett 3y ago> But I assumed that was dependent on the query planner; Problems that can be “fixed” with DISTINCT but really shouldn't, an accidental cross-join being one I've seen many times, be are common to many (all?) SQL implementations and I don't think they are treat much better by any of them (they are generally mistakes, so prioritising optimising for them is not a good use of the query planner engineering team's time). > Oracle SQL, as I recall, has/had specific constructs for giving hints to the query planner. SQL Server has index hints much like Oracles, along with locking hints (which I think Oracle has similarly), and so forth, which can tweak the query planners behaviour. They are best avoided if other options are available because: • they often only work well for a given data pattern, so you are just pushing the problem into the future when your data size has grown noticeably (in this context they are valuable as a temporary workaround, just make sure you treat them as such and work to remove the need) • they potentially block optimisations that future versions of the query planner/engine can perform by forcing it a different way around • sometimes they make explicit reference to objects (indexes usually), tightly coupling their names to things that others may not expect making accidental breaking changes more likely Postgres doesn't have the same sort of index hints IIRC, but does have options that constrain the query planner in similarly to try make it prefer a particular path and/or avoid another.
- barbegal 3y agoAt least generate some sizeable mock data before showing the query plans. The optimisations applied can be very different depending on the number of rows in each table.
- tomhallett 3y agoAny recommended resources (books, courses, etc) which go deeper on sql topics like this? Comparing and contrasting different ways of writing the same query, with respect to performance. The one book I’ve found and really enjoyed was “SQL Performance Explained”.
- pharmakom 3y agoI would love to use a SQL database where all tables are DISTINCT and unordered as a rule. A true relational model.