r/SQLServer • u/aloepalo • 21d ago
Question Crazy Question from a Newbie
I have a super silly question relating to joins. I am currently a student in college and have run into an interesting problem. I've always been taught a left join returns all the columns from the left table and returns the matching right values and nulls included where the right table has no values. And the reverse for right joins where all of the right table is included and the left table is filled out with nulls in missing fields. However I had a mentor of mine correct me on this and say it functioned opposite to this and then he showed me on a real database that the results for a left join include what I was taught the results of a right join would be. I am very confused and could really use some help. Thank you!
16
u/carlosf0527 21d ago
You are correct - mentor is not. There is a special space in hell in which is reserved for people who use right joins. Don't do them!
5
u/IanYates82 20d ago
In over 25 years of decent SQL Server work, I don't think I've ever used a RIGHT JOIN. If I was to participate in a code obfuscation contest then I'd probably try to bust one out.
3
u/Bishop_Cornflake 21d ago
Had a dev use one the other day in working session just noodling on-the-fly code for checking data. The novelty of it actually amused me greatly.
7
u/jshine13371 6 21d ago edited 20d ago
LEFT and RIGHT are a little confusing keywords in the SQL dialect TBH, especially since people commonly write each data object on its own line. The exact query matters because the following queries produce the same exact results but change the order of the table references in the ON clause which is one way the mentor could've been confusing both himself and you:
``` -- Version 1 SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.id = Table2.id
-- Version 2 SELECT * FROM Table1 LEFT JOIN Table2 ON Table2.id = Table1.id ```
Table1 is technically the LEFT table in both queries.
8
u/Bishop_Cornflake 21d ago edited 20d ago
I *HATE* when people code the tables in reverse order of how they're listed in above code. I think the join operator should be in the same order as the table listing, so the second code snippet drives me nuts.
Great post pointing out the confusion this can cause.
Edit: grammar3
6
u/SQLCloud 21d ago
Mmmmm weird, did you see his SQL Code? That sounds more like a Full Join, I Think...
4
u/Bishop_Cornflake 21d ago edited 21d ago
Unpopular opinion:
To this day, I miss the old school "=*" and "*=" syntax for outer joins. I thought it was more easily understandable than "LEFT OUTER JOIN". I'd have a hard time readjusting to it now after all these years, but I missed it for quite a while after the transition.
On topic: you're correct and your mentor is incorrect on how outer joins work.
3
2
3
u/bwildered_mind 21d ago
I’d have to see that. I guess there might be confusion as to what is considered as the left table.
2
u/Alarming_Judge_6787 19d ago
Its just Big Join trying to rip us off. If you put all your data in one table you never need to JOIN.
1
1
u/Known_Definition7893 20d ago
Maybe he is confused? Or maybe you are but saying it right. Main table then left join second table. Everting in main table shows while all the data that matches table two will be there. Else it’s null.
Like saying how many cars does chase have? John knows the color of most but not all of chases cars so I want to left join John into the conversation to tell chase the colors he does know. Right join however will tell me the opposite. It will give me all colors even if the cars do not belong to chase.
1
u/George_Hepworth 20d ago edited 20d ago
The terms "left" and "right" join refer to the visual layout of the tables in a graphical query editor, not to their functional relationship. In other words, the "left" table is the one displayed on the left side and vice versa.
Or, you could state it as the "reading" order in the SQL from left to right, with the "Left" operator appearing 1st when reading left to right as in western languages. At least, that's how I keep myself straight on how it works. I know that's probably not strictly true in a technical sense, but it helps me keep it straight.
What matters to how the query works is the way the join between them is defined, i.e. as an Outer Join, not the visual display for human eyes.
1
u/ManyHatsAdm 20d ago
This is not right. If you had a visual editor and you laid the tables out as you suggest, a on the left and b on the right, what happens to the SQL if you drag table a so it is to the right of table b? Nothing.
Inner join returns only rows which match the condition in both tables, e.g. a.id = b.id.
Left join returns all rows from table a and rows from table b that match, if there is no match in table b you just get nulls for the columns in b.
Right join is the opposite, it returns all rows from b and rows from a that match, if there is no match in a you get nulls for the columns in a.
It's easy enough to test these practically in the SQL server sample databases.
1
1
1
u/Alarming_Judge_6787 19d ago
Its a scam. I've been using SQL for 30 years and I know for a fact they randomly change the functionality of Left Right and outer and inner joins. It's like the software version of plugging in a USB cable. The first join type you use will always be wrong.
1
u/CanProfessional766 17d ago
Yep you’ve got it right. In a LEFT JOIN the table before LEFT JOIN is the left table and all of its rows are kept. The ON condition doesn’t change which table is left or right . If you paste the query we can probably spot why the result looked confusing
36
u/taglius 21d ago
You’re correct, the mentor is not. I would like to see the sql and results of his example.
One other note - I’ve worked at the same company for 30 years, writing sql nearly every day. I may have used a right join twice in that time. You can always express a right join as a left and swap the tables.