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.
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.
> "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.
>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.
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.
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
> 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).
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.
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.
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.
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.
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.
[+] [-] habibur|4 years ago|reply
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.
[+] [-] matwood|4 years ago|reply
Using left joins exclusively feels like it's papering over a larger issue that should be addressed.
[+] [-] unyttigfjelltol|4 years ago|reply
[+] [-] goto11|4 years ago|reply
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.
[+] [-] eurasiantiger|4 years ago|reply
[+] [-] dotancohen|4 years ago|reply
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.
[+] [-] lvl102|4 years ago|reply
OTOH, if I wrote my SQLs in that fashion during data science interviews, these young “scientists” will tell me I am a novice.
[+] [-] paul_f|4 years ago|reply
[+] [-] failedengineer|4 years ago|reply
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)
[+] [-] caaqil|4 years ago|reply
[+] [-] y4mi|4 years ago|reply
[+] [-] premek|4 years ago|reply
[+] [-] qpiox|4 years ago|reply
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|4 years ago|reply
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.
[+] [-] smt88|4 years ago|reply
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.
[+] [-] athrowaway3z|4 years ago|reply
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.
[+] [-] bryanrasmussen|4 years ago|reply
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.
[+] [-] jawns|4 years ago|reply
[+] [-] phendrenad2|4 years ago|reply
[+] [-] goto11|4 years ago|reply
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
[+] [-] pizza234|4 years ago|reply
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).
[+] [-] opportune|4 years ago|reply
[+] [-] Buttons840|4 years ago|reply
[+] [-] tacosbane|4 years ago|reply
[+] [-] bob1029|4 years ago|reply
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|4 years ago|reply
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.
[+] [-] gambiting|4 years ago|reply
[+] [-] pizza234|4 years ago|reply
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).
[+] [-] tsimionescu|4 years ago|reply
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.
[+] [-] kthejoker2|4 years ago|reply
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.
Don't leave tools in your toolbox.
[+] [-] ejb999|4 years ago|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?
[+] [-] unknown|4 years ago|reply
[deleted]
[+] [-] bmn__|4 years ago|reply
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?
[+] [-] unknown|4 years ago|reply
[deleted]
[+] [-] unknown|4 years ago|reply
[deleted]
[+] [-] dghf|4 years ago|reply
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):
That produces this: 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": This produces: (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:
Producing: 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.[+] [-] tibiapejagala|4 years ago|reply
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|4 years ago|reply
[+] [-] jokethrowaway|4 years ago|reply
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.
[+] [-] Noumenon72|4 years ago|reply
Could we speculate as to what his real name might be? Lefty? Joinathon?
[+] [-] sixtram|4 years ago|reply
[+] [-] spacemanmatt|4 years ago|reply
[+] [-] client4|4 years ago|reply
[+] [-] exikyut|4 years ago|reply
(If so, then bug, not feature; at multiple levels: I suspect sadly intentional.)
[+] [-] riidom|4 years ago|reply
[+] [-] Remko1981|4 years ago|reply
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.
[+] [-] karmakaze|4 years ago|reply
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.
[+] [-] sprior|4 years ago|reply
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.
[+] [-] sebazzz|4 years ago|reply
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.
[+] [-] dekhn|4 years ago|reply
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.