8 ms·
What's the strangest code you've seen a senior developer write? (2019)
- sixtram 5y agoI wrote a lot of T-SQL daily in the past 10 years. I think 99%+ of my joins are left joins.
- smt88 5y ago> "I have no idea if he is aware of other types of joins despite being the best database/SQL architect I’ve ever met or heard of." That just can't be right. Great SQL people understand set theory, query planning, and a lot of arcane RDBMS internals that most of us don't have to get into. I studied databases in college and have been writing SQL for 25 years, and I am regularly blown away by the knowledge people demonstrate on Stack Overflow. There is just no way a great DB architect or SQL programmer would only use left joins. The person who wrote this is likely just early enough in their career that they don't know what a truly great DB expert looks like.
- bryanrasmussen 5y ago>The person who wrote this is likely just early enough in their career I suspect that they were 18 in 2012 based on profile, so their career has been 10 years long so far. As far as no great would do X, not sure (as I avoid the relational DB side of things), but sometimes experts in a thing decide to only use a subset of the capabilities for personal preferences or because they feel it makes them more productive. So maybe in this guy's mind there was an SQL: the Good parts! which only included left joins.
- smt88 5y agoI think you could easily get 10 years into a web dev career and never meet a truly fantastic DB person. Most companies just aren't successful enough to need one. I am a reformed MySQL user, but one of my overly-normalized MySQL DBs has been running in production for almost 10 years without any optimization of queries, tables, or indices (beyond some naive indices I created when deploying it). My point is that you can throw an excellent RDBMS onto some decent hardware and serve millions of requests for a CRUD web app without really thinking too hard about it.
- athrowaway3z 5y ago>That just can't be right. Great SQL people understand set theory, query planning, and a lot of arcane RDBMS internals that most of us don't have to get into. Your likely misinterpreting what the author means with great. If your not at a scale where a team is pushing the micro optimization boundary, there is tremendous value in the ability to keep everything simple. Having the entire org operate using the same frame is great. What's more, it's not uncommon for great simplicity to be mistaken for 'obvious', and excessive complexity be mistaken for great work.
- BiteCode_dev 5y agoWhich is also why ORM are so popular despite being suboptimum. Not because they save you from writting SQL, but because they force people to standardize : to define a single point of entry for they data, to centralize the schema, integraty checks or signals, and of course, normalize the way the query are written. This has greater value than perf for a lot of orgs.
- jawns 5y agoIf this architect was that wedded to LEFT only, one would think he would have been outspoken about why. Most of the great programmers I've known have had two characteristics: They'll tell you very confidently about what you SHOULD do, and they'll tell you very loudly about what you SHOULD NOT do.
- kcplate 5y agoI have always said that if if you put 10 great programmers into a room together you would have 10 people who are absolutely convinced they are the smartest person in the world surrounded by the 9 dumbest people in the world.
- phendrenad2 5y agoMakes sense. When you think about it, all joins are just a left join (perhaps joined with an empty table), and some in-place filtering. Inner join? That's just a left join, and then skip any row where the joined table returned nulls. Full outer join? That's just two left joins against an empty table, sorted, and you use dual iterators to step through them in lock-step.
- andrewclunn 5y agoI imagine a theoretical programmer who only used while loops, or did everything using recursion. Not reason to ever touch a for loop really. There are plenty of shortcuts that obscure what's happening under the hood. If you can do it all "closer to the metal" as it were, then why bother?
- xupybd 5y agoI think having a preferred iteration strategy is important. At my first job I was taught consistency consistency consistency. The more ways you do thing the more complexity you add to the code. If you have a project with the same patterns over and over again it becomes easy to learn and easy for Juniors to contribute to.
- xupybd 5y agoYeah and if you put everything in as left joins filtering only happens in the where clause. It becomes easier to understand why rows are missing. I've done this in the past when I wanted to make it obvious that x was getting filtered out of the result set.
- dotancohen 5y agoYou can filter on the left table by putting the conditions in the ON clause.
- xupybd 5y agoExactly you can but then your query has multiple places to examine if you want to know what out you should expect. By having it all in the where clause it can read a little better.
- jokethrowaway 5y agoNothing unusual there. Left joins (or right joins, but that sounds like the opposite of the way I see tables in my head) are the most useful in day to day life. I don't think I ever shipped a crossjoin for perf reasons and very rarely I needed the intersection of two tables. Most likely the op never was in a situation to need those.
- setr 5y agoIf he really never used an inner join though, that’d just be weird. I’ve never come across an outer join or right join usecase in practice… but definitely inner joins…
- goto11 5y agoSure, but an inner join is just a left join with a not-null constraint. More syntax, but not a lot. I can see how someone might prefer left joins for consistency.
- xupybd 5y agoCross joins are great against calendar tables. If you want to generate a periodic item.
- opportune 5y agoI think I understood that he was both great at SQL and only used left joins after the 13th paragraph, not sure though.
- rat9988 5y agoI scrolled just to know what people are commenting. The practice or writing style. I'm surprised you are the only comment who mentioned it. Maybe most hacker news readers don't get past the title or the first paragraph.
- opportune 5y agoTo be honest I think this post was poorly written, and it comes across as the author getting paid by the word. So I was surprised to see the post get so upvoted
- gambiting 5y agoAs someone who doesn't work on databases - could someone please explain what is a left join any why using it exclusively is a problem?
- bmn__ 5y agoIt's not necessarily a problem. It's just excentric. How would you feel about a programmer that solely uses `nand` as boolean operator, or `<` as relational operator, expending some effort to transform any expression from the domain so that it conforms to this stylistic restriction?
- pizza234 5y agoAn inner (standard) join returns a row in the result, when the join finds a match between two tables, and no results when there is not match. A left join always returns at least a row; when there is no match, the part of the result (if selected) that corresponds to the right table, has NULLs. There are many important consequences of this, one of which is that left joins enforce table ordering; inner joins can be reordered by the optimizer if iterating them in a reordered way gives faster access (imagine two nested for loops, and swap the inner loop with the outer one).
- deleted 5y ago[deleted]
- tsimionescu 5y agoThe basic setup is that you have some data in two different tables, and you want to get information from both. A left join will take (select * from tableA left join tableB on $condition) will return data where each item has all of the columns from tableA and all of the columns from tableB. Each row will be a row from tableA, with all of tableA's columns filled in. Then, the tableB columns will be filled in for that row as well if there was a "matching" row in table B (according to $condition, which is any condition you want, like in an if statement). For the rows of A that didn't have an equivalent in B, all of the tableB columns will be filled in with NULLs. In contrast, you have 4 more Joins: the Right Join is identical to the Left Join, except that it flips the direction, so it's never necessary. An Inner Join will only return rows that match the condition in both tables, similar in concept to a set intersection. This is a very commonly useful operation, but it can be replaced by something like "select * from tableA left join tableB on $condition where tableB.Column is not NULL". Then, there is the "Full Outer Join", which gives you all rows in both tables; all of the rows that match appear only once and have all columns filled in, while all those in tableA that don't match still appear, and same for tableB, with NULLs for the columns of the other table. This can be obtained by doing a UNION of (tableA left join tableB) and (tableB left join tableA). Finally, there is the cross join, which is basically the cross product of the rows of the two tables. That is, for each row in tableA, you get a row with columns set to the values of that row in tableA, and then the columns from tableB set to the values of each other row in tableB (so, you have m*n rows, if tableA has m rows and tableB n rows). This is very very rarely useful in practice.
- Noumenon72 5y ago> Jared if you’re reading this, I’m sorry for using Jared as the fake name. Your real name fits much better. Could we speculate as to what his real name might be? Lefty? Joinathon?
- deepsun 5y agoI suspect it's the author themselves.
- dotancohen 5y ago> Lefty? Joinathon? Samuel Quentin Lucas?
- tibiapejagala 5y agoI sometimes use left join to protect me against errors somewhere else. Let’s say you do an inner join in insert into … select statement to find some other entity which 100% should be there. If something else is screwed up you might silently filtering out rows. With left join you keep everything, but not null constraint on the table protects you and turns it into an explicit error.
- Moru 5y agoYes, have seen this a number of times. Convert to left join and some sanity checks and everything works again. I'm also one of those that mostly use left joins, not sure what the article is on about :-) The other joins have their uses but most of the time it's lefties.
- client4 5y agoWhen did Quora start requiring logins to view content?
- riidom 5y agoNot sure what you are seeing, but I don't have an account and the "continue reading" button is not a paywall indicator, it just works when you click it - in case you refer to that?
- peppermint_tea 5y agoWhen I click on the comments icon, I also have the choice between : -1 continue with google -2 continue with facebook -3 login -4 sign up with email -5 close and read quora "close and read quora" does not show me the comments...
- riidom 5y agoYou are correct, I didn't notice there are comments before. Also can't read them.
- dotancohen 5y agoI get the "You’ve reached the monthly visit limit" even though I don't visit Quora often, it's one of the sites I avoid.
- riidom 5y agoYea I usually also don't use it. For monthly limits in general, a quick research about "Cookie Remover" extension may prove useful :)
- exikyut 5y agoYou might have recently viewed other pages on the site. Clearing your cookies should fix it. (If so, then bug, not feature; at multiple levels: I suspect sadly intentional.)
- bob1029 5y agoWe have a bit of an argument going on in our shop regarding this right now. My perspective is that the left join is essentially null coalescing for relational systems. Only in queries where the joined table contains optional facts would a left join be required. Our system is very consistent throughout, so only a few classes of joins actually need to be left joins. Despite this, we are using it in a lot of other places than would otherwise be required. At this point, I am perfectly happy staying out of the way because others on the team are producing useful SQL. It might not be to my exact preferences always, but it gets the job done regardless.
- erikpukinskis 5y agoI’m curious, you admit that “generally use inner joins” is a leaky abstraction, because there are corner cases where left joins are needed. Why not just use the non-leaky abstraction “always use left joins” and then an entire class of errors and discussions can be avoided? Increasingly as I get older, I see the job of architect as “have conversations for the last time” so that devs can focus their “conversation quota” on the things that can’t be avoided, thus moving faster.
- bob1029 5y ago> Why not just use the non-leaky abstraction “always use left joins” and then an entire class of errors and discussions can be avoided? This is why I am happy to stay out of the way of others. If I were to impose a strict "always use left joins", there would be a different problem of similar flavor. There are some team members who prefer to be more precise with the intent of their joins, and others who prefer to be more defensive. As long as the end result is as desired, I am happy and the customer is happy. What I focus on the most throughout is making sure that any non-malicious query can run without worrying too much about performance. We scope our data sets so that even recursive table scan scenarios can complete within seconds. This eliminates the most onerous class of discussions in my experience - those of premature optimization.
- habibur 5y agoHad been writing SQLs for decades. In the beginning tried all kinds of joins, but every one of those had some unwanted side effect. Especially where there were un-expected nulls. Figured out LEFT JOIN eliminated all of those and produced what I want. Had been using LEFT JOIN since. I ever remember only one time when I didn't use left join, because that case needed a full join.
- eurasiantiger 5y agoSame experience here. LEFT JOIN does exactly what I want every time, and keeping to it makes complex queries easier to work with. Other join types simply aren’t necessary in the majority of web/CRUD operations even with complex relationships. If they feel that way, the query is likely somehow ass-backwards to begin with.
- dotancohen 5y agoI also remember only a single "can't-use-a-left-join" problem. Left joins express "Give me these things, and these other sub-things related to them". Object oriented programming lends itself towards the thing-with-subthings (and collections of things-with-subthings) so I feel that the left join is the default choice for most applications which are structured with the classic OOP approach. I wonder if full joins are more common in other programming paradigms, such as whatever those crazy lisp folks talk about.
- caaqil 5y agoI think we found Jared.
- paul_f 5y agoCount me in. Left joins only. It works, is fast, and I only need to remember one type of join
- lvl102 5y agoThis is so much me. I used to be an SQL junkie back in my 20s but the work I did was critical. As in if my query was wrong even due to some weird software quirk/edge case/bug, that could mean a few million dollars or even into the billions. That type of pressure leads you to write SQL (or code in general) in a very specific way. OTOH, if I wrote my SQLs in that fashion during data science interviews, these young “scientists” will tell me I am a novice.
- patja 5y agoPost title is missing the word "developer". A senior is an old person, which is (usually) not the same at all as a senior developer.
- spacemanmatt 5y agoFTFY: Senior Query Language
- alliao 5y agoI think if you know your data well, this is totally workable. If you don't, then as much as inner join as you can and let the engine do it's work.. SQL at the end of the day is supposedly a declarative language
- deleted 5y ago[deleted]
- alexpetralia 5y agoI like to think of full outer joins as the most conservative join and the inner join as the least conservative join (quite literally "conserving" rows).
- Joeboy 5y agoWhen I go to the link, the top answer is about an if..else where the else will never be called. Searching the page for "left join" finds nothing.
- Brian_K_White 5y agoYou didn't intend to talk about this but... I think it's not necessarily wrong to write a line of code that is never reached, especially for code that may be changed later, and maybe by someone else, which is really all code ever. You might write the no-op or unreachable else branch just so that the placeholder is there, to highlight that it needs to be considered in case the original condition is ever changed. It's only unreachable today with todays condition and maybe even just todays data or maybe even just todays version of the language. Tomorrow another programmer could change the if condition, or the data could start to fall through that condition, or even some implicit behaviour of the language could change which ends up changing how the condition is evaluated.
- Joeboy 5y agoYeah, I've done things like raise Exception("This should never happen") and feel no need to apologize for it.
- Remko1981 5y agoWhat a lot of people do not realize is that they are making implicit inner joins because of the way they use the where clauses. When you say 'where left join column = some value' you exclude all rows containing NULL in that column. In other words all rows that did not join in addition to rows which contain a NULL in the column.
- spacemanmatt 5y agoI totally get standardizing on techniques like this, but still caution that it should be done after getting to know the different join styles, rather than in lieu of getting to know them. It might sound small, but this is the difference between a junior dev and a senior dev: Rules are great simplifiers, but understanding why they apply (and thus when they don't apply) is better.
- daniel-s 5y agoHave used SQL, not super advanced. What are the "advanced" SQL and RDB things to learn after you learn how to create and populate tables, query, join between tables?
- nojito 5y agoWindow functions.
- kthejoker2 5y agoNearly 30 years of SQL experience here, I tell new people there's really no advanced SQL, just "exotic" SQL. (Or a joke: "There's 3 types of Advanced SQL. Really long SQL. Exotic SQL. And SQL no one should have written.") The "advanced" part is: 1) Set Theory - really understanding set theory so you can model datasets appropriately and write efficient SQL 2) The Toolkit - knowing how the major concepts of SQL (join types, CTEs, table valued functions, windowing, aggregation, conditional logic, indexes, constraints, normalization/denormalization, etc.) contribute to #1 3) Performance Analysis - understanding how to analyze the performance characteristics of a query so you can apply #2 to #1 to achieve good results And usually the difference I see between juniors and seniors is if you give them 2 "advanced" queries and tell them one runs fine and one doesn't, seniors very quickly know which one is bad and why it runs bad ... juniors aren't even confident the two queries will run because they contain syntax or patterns they're unfamiliar with.
- denton-scratch 5y ago> really long SQL Some systems generate really long SQL queries; I'm thinking of you, Drupal! I haven't worked with D8, and only a little with D7. But D6 would routinely produce SELECT queries with 20 tables joined. Drupal is notorious for caching nearly everything, including the results of SQL queries, because those queries were essentially untunable (and the "cache" was usually yet another database table). "Flush all of the caches" was the first advice to anyone struggling with a D6 issue.
- denton-scratch 5y agoHow to use EXPLAIN PLAN. As a contractor, I was once asked to improve the performance of a query that had a main table with many millions of rows. EXPLAIN PLAN revealed that the query planner was making some bad choices, despite the table statistics being up-to-date. I bullied[0] the optimiser into making some better choices, and got a 100x performance improvement. For large databases, the query plan can make the difference between a query taking a second and taking an hour. Unfortunately the format of query-plan output from EXPLAIN PLAN is unstandardised and vendor-dependent. [0] "bullied": This was Oracle; you could use "hints" to force the optimiser to change it's behaviour. If you don't have hints, then sometimes transforming a JOIN query into a query using an inner SELECT will expand the optimiser's consciousness. It all gets a bit voodoo, if the optimiser is stubborn and you don't have hints.
- yen223 5y agoI don't think I've ever had a good reason to use a right join
- i_like_waiting 5y agookay, I would understand not using right join and inner join. But how can you be a DBA and not use full outer join or cross join? Its also true that unless your work is DE or BI analyst, I guess people are not using SQL up to that point.
- denton-scratch 5y agoA cross join is nearly always a bad thing, because the result-set is massively larger than the joined tables themselves. Unless the tables being joined are small, you get performance and memory problems. I've never used a cross join except when playing around. I've rarely had to use a full outer join; I think that if you encounter that need, it bespeaks a problem with your schema. But sometimes DBAs are extremely resistant to schema changes in production, so you have to work with the schema you have. FWIW, SQL isn't the core of my trade; I'm not a data analyst, I'm just a normal dev with SQL as one of the things in my toolkit.
- i_like_waiting 5y agoCross joins I use to ensure there are no "holes" in my table that I am using as backbone for subsequent queries, (e.g. by aggregating information from multiple tables based on date and country) For full outer join, well you need to create dim tables sometimes half ad-hoc and full outer seems to be much faster solution than UNION
- qpiox 5y agoThere is nothing strange in this practice of only using a certain type of joins. If you are right handed, and have a practice to write the joins in the direction one->many (or person->children, or in the opposite direction of a foreign key) then you will always write the join as: from person join children Then, if you are not a beginner and know a bit about nulls and their side-effects, the best thing that you can do to prevent the risk of missing data is: from person left join children Why? Because this will help so that you will never miss those persons that do not have children. So, depending on the type of the application, it may be the case that outer joins are nearly always needed and it maybe be a part of quality assurance policies to always do a left join, so that most queries are easily readable by most of the team. If you are left handed, then perhaps you will write the same query as: from children right join person full outer join is not needed at all if you have enforced referential integrity everywhere - you will only need a left (or right) outer join (depending on the left-to-right or right-to-left reading preference) in databases that do not have referential integrity enforced on all true foreign keys, full outer join will probably be the only right type of join. Otherwise, you will have problems with many scenarios.
- bryanrasmussen 5y agoOk well I am right handed and that's how I would write it, but I figured that's cause I just don't know hardly any SQL. Do you have any data on the handedness of people affecting the directing in which they write joins? It seems a strong enough assertion that somebody should have done research.
- CraigJPerry 5y agoNot the OP but i doubt he literally meant left and right handed people. I read it as a fun, lighthearted way to differentiate two equally valid but opposing ways of thinking about the problems.
- tacosbane 5y agoThere is not a single example about why this was "wrong" (e.g., filter equivalences to other join types) or a single acknowledgment of good reasons the admitted technical and architectural SME during your first-ever real-world experience might have for doing this. So I'm guessing this author has more than once incorrectly calculated a mean because he innered when he should have outered.
- tb131 5y agoArchive without JS + works on VPNs etc. : https://archive.ph/d3xGl https://archive.ph/d3xGl
- Buttons840 5y agoThis is all imagined retrospective. Jared never actually turned down a PR over a right join or the like. Maybe if the OP had ever used a right join Jared would have shrugged and said "not how I would write it, but it works and is understandable" and moved on.
- Buttons840 5y agoTangentially, the weirdest coding practice I've seen was a company with a single PHP developer who didn't know what a function was. The entire company ran on a 5000 line top-to-bottom script with a cyclomatic-complexity in the tens of thousands, and was executed by Windows Scheduler. At one point I counted controll structures nested 17 deep, ifs inside loops inside ifs inside loops, etc. Also, the one developer didn't use version control and was resistant to my attempting to teach him. I worked on the website though, didn't touch that script with a 10 foot pole.
- chiph 5y agoMention "bus factor" to their boss. https://en.wikipedia.org/wiki/Bus_factor https://en.wikipedia.org/wiki/Bus_factor
- Buttons840 5y agoActually, the boss understood the script well enough to make some changes and fixes, but he didn't work on it full time. The script was mostly in maintenance mode.
- dekhn 5y agoI'm not a sql expert but I've used sql for over 20 years and I think I've only used one type of join. It's not even syntactically specified as a JOIN- just SELECT * FROM a, b, c WHERE a.id = b.fid AND b.id = c.fid I think this is known as an INNER JOIN? I dunno, I came from science where cross product and filtering are approached in a very different way.
- rvr_ 5y agoThe pervasive use of ORMs with lazy-loaded relationships works like a bunch of left joins, even if they are done as separated queries.
- karmakaze 5y agoI wouldn't do this myself, but I can see how this pretty much gets you everything you need. LEFT and RIGHT joins are mirror images, so as we mostly read-L-to-R prefer LEFT. Both inner and outer joins are useful. It's worth noting that putting any WHERE condition on the right table-expression columns of a LEFT JOIN makes it into an inner join unless the condition is `IS NULL`. If you have no particular condition but want an inner join, you can always add right_table.id IS NOT NULL. I've met brilliant people with idiosyncrasies, so I'd chalk it up to a consistency/single-point-of-variance choice where only the WHERE clauses sets the join-type. I'd be curious to know if the clauses that change the join type always came first. My pet peeve is writing LEFT OUTER JOIN, there's no such thing as a LEFT INNER JOIN so it's just noise--please stop.
- sebazzz 5y agoAs far as joins go, in T-SQL I mostly use INNER JOIN and LEFT JOIN. I've rarely, if ever, used a RIGHT JOIN. I suppose a RIGHT JOIN is reverse thinking, which is why I don't use it. A FULL OUTER JOIN I've never had a use-case for I think. Then we have CROSS APPLY, which I've used once or twice. And finally CTEs which I use a lot, in addition to widow functions.
- sprior 5y agoBesides the comment about the developer who only used left joins there was also this one: I worked with a ‘senior developer’ who, on a really big project with lots of different coders around the country, prototyped the implementation in prolog including the JUnit tests (in prolog too). I think I actually know the answer to this one. I don't remember whether it was while I was still in school or later, but once heard someone say that they always picked a language to implement a prototype in that would never under any circumstances be used for the final system so that it was impossible to try to hack the prototype into the final system. And for the record I actually like Prolog for certain projects.