Had 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.
As someone who has been writing sql for 20 years (yikes I'm old), a left join would convey to me the author expects unmatched records in the joined table. For that reason alone I would use inner joins to convey inner join semantics, and fix whatever is 'un-expected'.
Using left joins exclusively feels like it's papering over a larger issue that should be addressed.
As far as I'm concerned, LEFT JOIN is simply shorthand for LEFT OUTER JOIN, and is semantically equivalent to a RIGHT JOIN, but with the order of the tables reversed. If what you really need is an INNER JOIN, and you use a LEFT JOIN instead, then you have a load of processing to do on the result-set.
So if this guru "Jared" is only ever using LEFT JOIN, then either he's addressing a rather constrained problem space, or he's isn't that much of a SQL guru. Maybe he designs schemas in which LEFT joins are always equivalent to INNER joins?
[Edit] I'm wrong; you can, of course, get the effect of an INNER JOIN by using a LEFT JOIN with a NOT NULL join condition. But I find the latter hard to read: there must be some reason this guy has used an outer join here, when it looks as if an inner join fits the bill?
Exactly. It's always inner joins unless there is a reason not, and then there should be logic to deal with the null results from the outer table. It's not strange to always do left outer either, it just makes reasoning about the query easier (start with the driving set, the inner/outer join the others as left joins). Mixing right and left outer always confuses me, and full outer joins are difficult to optimize, so I usually do left outer joins with unions in the rare case that I need a full outer join.
If you're writing code, you're strategy is clearly better because it documents an expectation.
If you're dropped in a database which is unknown territory, say to debug an issue, left joins represent a conservative guess, maybe even allowing to follow up with verification querys: where right join side is null.
I'd read the different responses here as people having different roles.
It's not a conservative guess. It's objectively wrong. Inner join means "include only matching records," and if nulls are being dropped, it's because the developer doesn't know that in SQL, null != null, and they need to address the defect in the code that excludes that case.
If you're debugging something, there tends to be a defect to address in the vicinity ;-) Maybe also add there is a difference between an ad hock query, where conservative guesses are acceptable, and queries built into code, where you'd better know what you're doing.
I've done it a few times, typically in a situation where some random contractor dropped a minimal effort database design right into production. No constraints like not null of course, that would mean the application shows an error and contractor can't have that because it would imply they did something bad.
So the DB chugs along for months, accreting damaged data. Then something finally crashes bad enough to wake up the management, which instantly switches from 'everything is perfect lalalalaaa' messages into blind panic mode.
So you get thrown into the mess, sometimes without even knowing what the application is supposed to do, with the directive: Fix this, we're bleeding money. After looking around for about 5 seconds, it turns out everything is objectively wrong. You can't fix all fires at the same time, you have to put out one at a time, and try not to make it accidentally worse. That's when you use left joins as a conservative guess, at least until you've built up enough knowledge.
I never understood why there were so many other kinds of joins. Using only LEFT joins makes it easier to hold a mental model in mind, because you're always running left to right, superior to dependent. Occasionally I would flip to a RIGHT join for testing or adjusting and the structure immediately would be much harder to hold in mind.
> In the beginning tried all kinds of joins, but every one of those had some unwanted side effect.
This makes it sound more mysterious than it is. Joins does not have side-effects, they are just different operators. It comes down to if you want to include unmatched rows. Left join: Include unmatched rows from the first table. Right join: Include unmatched rows from the second table. Outer join: Include unmatched rows from both tables. Inner join: Don't include any unmatched rows.
Left and right join is of course the same, the only difference is in which order you list the tables. Left join seem more intuitive to me when writing queries, but logically they are equivalent.
Same 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.
I 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.
This 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.
This makes my head and heart hurt to see so many people agreeing with this.
There's a right, and wrong, time for using left joins. "all the time" is the wrong time. If you need to join on all existing records, including null records, you need the trinary equality operation.
Also... there's a right join... if you're writing complex enough stuff long enough, you'll eventually hit a query where it's just easier to do the same thing you've been doing on the right side.
I can't say for sure, because it's implementation dependant, but this is going to slow down your results on larger data sets as well, since you won't be excluding joins (in memory) by excluding data (during the selects from individual tables)
There 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.
Ok 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.
Not 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.
> "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.
>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.
Which 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.
>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.
I 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.
If 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.
I 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.
Makes 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.
Right join is just left join with the clauses switched, so it makes sense to use left join consistently. Inner join is just a left join with a 'not null' condition. I prefer inner join for conciseness, but it makes sense to always chose left join for consistency. Full outer joins are rarely needed in my experience.
In any case, all the joins are just syntactic sugar over cross product. AFAIK SQL did not have the join syntax initially, you just did SELECT foo, bar WHERE foo.id = bar.foo_id
You are right, I'm wrong. You can get left, right and inner join by applying constraints to an outer join, but you cant get an outer join from a cross product.
> When you think about it, all joins are just a left join
Not really; using left joins enforces a specific table order in the query plan.
It's possible to optimally use left joins because one can either guess the optimal order (which is a bad habit, though), or can observe the query plan and emulate it.
My guess that this developer didn't trust the optimizer to do a good job at ordering, and he wanted to enforce it, but that is generally not the case with modern engines (of course, there will always be exceptions).
> that is generally not the case with modern engines (of course, there will always be exceptions)
In my experience, the exceptions are more than the straightforward queries I can write and forget, so much that SQL grammar feels like the German grammar: Here's what you use in this scenario, except [goes on to list a million exception cases most of which makes no logical sense].
This is especially true if you are doing complicated queries across products in a multi-product multi-tenant multi-team database. Writing the idiomatic query takes a few minutes, then hours of fighting against the query planner.
Going bonkers with left joins and/or CTEs usually "fixes" the "problem". I usually leave a commented-out sane version after me for the people who can do better, and not to be blamed with writing mortgage-code.
Neither German nor SQL (with any RDBMS) is my native language anyway, so I can publicly admit my struggles without feeling too embarrassed :)
Yeah 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.
Exactly 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.
>When you think about it, all joins are just a left join
Absolutely not. All joins can be related, or explained by using left join as a reference, but there's no such thing as a "default join", or perhaps there is, and it's up to the implementation of the database. Taking the analogy further, all joins are outer joins, just apply some filters, right? Or a left join is just an inner join, and then you include the rest of the left table. It's worthwhile pondering about this, but "all joins are just a left join" is a moot point.
I'm surprised nobody else has mentioned this yet - a LEFT JOIN can be more efficient than an INNER JOIN precisely because the database doesn't need to enforce the filter during the query plan.
This comes up for me most often with views, when a query referencing the view doesn't end up using all the columns - when a LEFT JOIN is used, the database can potentially skip querying those tables entirely, but not with an INNER JOIN.
I 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?
I 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.
I 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.
To 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
Tangentially, 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.
Actually, 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.
There 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.
We 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.
I’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.
> 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.
An 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).
The 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.
Forget about the specifics - in this case it's Maslow's hammer.
Different types of joins exist for a reason - they address different requirements.
Using one type of join even when those different requirements need to be met is suboptimal.
In LEFT JOIN's case, it's usually not fatal (i.e. completely unable to meet the requirements) but it will definitely make things perform slower, be more complex to maintain, be harder to explain why it was chosen to other engineers (see OP), etc.
>>could someone please explain what is a left join any why using it exclusively is a problem?
reply
There is nothing wrong with it per se, it depends on the question you are trying to answer - gauging someones competence based on the absolute number of times someone uses a left join, vs right join, vs outer join during their career is silly - almost any query can be written multiple ways to get the exact same results. What matters is did the developer get the correct results.
It's good to have a sense of what other join types do, but if they don't get you to the answer you want any faster or better, so what?
It'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?
Imagine we've got an EMPLOYEES table, that for each employee gives their employee id, their name, and the employee ID of their direct manager:
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID
----------- ------------- ----------
1 Alice the CEO NULL
2 Bob the Director 1
3 Charlie the Grunt 2
We can get all employee-manager relationships with an inner join that finds every pair of rows in the table where the MANAGER_ID on one row matches the EMPLOYEE_ID on the other:
SELECT emp.EMPLOYEE_NAME, mgr.EMPLOYEE_NAME AS MANAGER_NAME
FROM EMPLOYEES AS emp
INNER JOIN EMPLOYEES AS mgr
ON emp.EMPLOYEE_ID = mgr.EMPLOYEE_ID
which produces this:
EMPLOYEE_NAME MANAGER_NAME
------------- -------------
Bob the Director Alice the CEO
Charlie the Grunt Bob the Director
Note that Alice does not appear in the left-hand column, because she has no manager, and Charlie doesn't appear in the right-hand column, because he doesn't manage anyone.
If we want to list all employees, with their managers where they have them, we can use a left outer join, which returns all rows on the "left" side of the join regardless of whether they have matching rows on the "right" side -- like this (cutting out bits of the query repeated from the previous example):
SELECT ... LEFT OUTER JOIN EMPLOYEES AS mgr ON ...
That produces this:
EMPLOYEE_NAME MANAGER_NAME
------------- -------------
Alice the CEO NULL
Bob the Director Alice the CEO
Charlie the Grunt Bob the Director
If we want to see who (if anyone) every employee manages, we could use a right outer join, which is similar to the previous but takes rows from the "right" side whether or not there are matching rows on the "left":
SELECT ... RIGHT OUTER JOIN EMPLOYEES AS mgr ON ...
This produces:
EMPLOYEE_NAME MANAGER_NAME
------------- -------------
Bob the Director Alice the CEO
Charlie the Grunt Bob the Director
NULL Charlie the Grunt
(To be honest, this is even more contrived than the previous examples: you can achieve the same result by reordering things in the left outer join, and that would come more naturally to most people, including me. And you would probably swap the order you display the columns in. But I include it for completeness.)
Finally, you can get a combined list of employees and their managers (if any), and potential managers and their direct reports (if any), with a full outer join, which returns rows from both sides of the join, regardless of whether they have matching rows on the other:
SELECT ... FULL OUTER JOIN EMPLOYEES AS mgr ON ...
Producing:
EMPLOYEE_NAME MANAGER_NAME
------------- -------------
Alice the CEO NULL
Bob the Director Alice the CEO
Charlie the Grunt Bob the Director
NULL Charlie the Grunt
Using a left join exclusively isn't a problem, as long as it's returning the data you need. If you use a left join where your data requirements actually call for a different type of join (e.g. "exclude any employees without a manager"), it could be a problem. You could get round it by adding a WHERE clause (e.g. "WHERE mgr.EMPLOYEE_ID IS NOT NULL"), but that's a bit ugly and hacky.
Oh, absolutely. In some (many? most?) SQL dialects, you don't have to write INNER JOIN, either: an unqualified JOIN keyword implies an inner join. I was just trying to be explicit.
I 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.
Yes, 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.
If 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…
Sure, 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.
I 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.
Not 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?
What 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.
I 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.
Besides 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.
As 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.
I'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.
This 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.
I 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
Have 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?
Nearly 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.
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.
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.
I 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).
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.
A 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.
Cross 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
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.