SQL & the JOIN Operator

Question

Post reply

SQL & the JOIN Operator

Viewing 15 posts - 46 through 60 (of 98 total)

You must be logged in to reply to this topic. Login to reply

arun55 Grasshopper Points: 17 More actions · Answer 1

Right Now, results are same as there are no null values for b.date2 .

"b.date2 is null" is a precautionary statement.

That's why, i want to know whether logically both queries are same or not.

Thanks,

Varun.

David Walker-278941 Ten Centuries Points: 1064 More actions · Answer 2

ryan.mcatee (10/7/2009)
Speaking of queries, the ones in the article are simple examples, but when you're pulling many columns and joining on multiple keys, repeating full table names over and over gets tedious to read and write...
I think it really just depends on the situation. I often abbrevate (much to the chagrin of my boss) because, like you, I don't find it feasible to string together 8-9 fields with REALLY long names as is often necessary for one of our databases. It's one of those "that's great in practice, but how does it work in theory" arguments...
IMHO. 🙂

Tedious to write? No, that's what Copy and Paste are for. Tedious to read? I don't agree. "TableName" is much less tedious to read than something like "jqz".

As for the examples with ridiculously long table names, sure, that can happen. I still don't mind. Long table names are not hard to read.

As for aliasing tables with names that are more meaningful in context than the original name, there's nothing wrong with that. BUT THAT is not the situation here, now, is it? Very few people have noticed that; instead, most are arguing against my reasonable points, using strained examples.

My original point was -- and almost everyone has overlooked this point -- PLEASE don't always and automatically use table aliases in every single explanatory, introductory-level article without specifying that they are optional. And maybe add a sentence on when to use them. If you say "Here is the syntax of Join" and your examples ALWAYS use table aliases, then naturally, people are going to think that Table1 must have an alias of something like "Tbl1" to make the syntax complete and correct. And I claim that "tbl1" is harder to read than Table1 because it lacks the vowels that English has.

davidpenton SSC Rookie Points: 41 More actions · Answer 3

The bigger concern that I have is when people *do not* use any alias, even for a simple query. Then the next person comes along, adds a join without adding aliases. Then you are stuck trying to figure out what columns go with what table. I think it is short sighted to say only use a table as an alias as much as it is to say use a "dumb" alias. Then, you'll end up with 4-part naming on columns effectively turning them into 5-part names. There is nothing easy [for reading] about that.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

sushila (10/7/2009)
Jeff - ...are on your way to becoming a famous author as well...could you not teach people the art of keeping an even tone..?!;-)

Hello my long-time and trusted friend. Long time no "see".

I don't know about becoming a "famous" author, but it's one of the many fires I have sticks in. So far as keeping an even tone goes, it is an art and some will never learn it. Even I slip once in a while (Ask Steve). I've found that it can't be taught... it can only be learned. Besides, if we all kept an even tone, folks wouldn't appreciate the even tones of others so much. 😛

Heh... there's a silver lining to every cloud. 🙂

Don't be such a stranger around these parts, eh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wagner crivelini SSC Eights! Points: 922 More actions · Answer 5

Hi David

I understand your point.

But it came out so naturally to me writing statements with aliases that it never crossed my mind to say they are optional.

But I should have done it, I have to agree with you on this.

But as some of the guys said here, I feel using alias is more about someone's style than anything else.

I feel using meaningful aliases (and I agree with Jeff's emphasis on the word meaningful) make queries much more readable.

Again someone might argue on what exactly is "meaningful".

In the examples I showed in the article, as there were 3 tables called Table1, Table2 and Table3, I thought it would be meaningful enough to alias them as T1, T2, T3.

But this also might be subject for another discussion ......

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

David Walker-278941 (10/7/2009)
ryan.mcatee (10/7/2009)
Speaking of queries, the ones in the article are simple examples, but when you're pulling many columns and joining on multiple keys, repeating full table names over and over gets tedious to read and write...
I think it really just depends on the situation. I often abbrevate (much to the chagrin of my boss) because, like you, I don't find it feasible to string together 8-9 fields with REALLY long names as is often necessary for one of our databases. It's one of those "that's great in practice, but how does it work in theory" arguments...
IMHO. 🙂
Tedious to write? No, that's what Copy and Paste are for. Tedious to read? I don't agree. "TableName" is much less tedious to read than something like "jqz".
As for the examples with ridiculously long table names, sure, that can happen. I still don't mind. Long table names are not hard to read.
As for aliasing tables with names that are more meaningful in context than the original name, there's nothing wrong with that. BUT THAT is not the situation here, now, is it? Very few people have noticed that; instead, most are arguing against my reasonable points, using strained examples.
My original point was -- and almost everyone has overlooked this point -- PLEASE don't always and automatically use table aliases in every single explanatory, introductory-level article without specifying that they are optional. And maybe add a sentence on when to use them. If you say "Here is the syntax of Join" and your examples ALWAYS use table aliases, then naturally, people are going to think that Table1 must have an alias of something like "Tbl1" to make the syntax complete and correct. And I claim that "tbl1" is harder to read than Table1 because it lacks the vowels that English has.

I agree with all of your points about making reasonable aliases and setting a good example for such things especially in introductory-level articles. But I disagree with you on two points... so far as I'm concerned, properly formed table aliases aren't optional and neither is the two part naming convention.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

davidpenton (10/7/2009)
The bigger concern that I have is when people *do not* use any alias, even for a simple query. Then the next person comes along, adds a join without adding aliases. Then you are stuck trying to figure out what columns go with what table. I think it is short sighted to say only use a table as an alias as much as it is to say use a "dumb" alias. Then, you'll end up with 4-part naming on columns effectively turning them into 5-part names. There is nothing easy [for reading] about that.

Boy howdy! Do I ever agree with that! So much so that I'd rather see poor alias names like a, b, c than to see no aliases at all. If you're in an unfamiliar system, not having any aliases (or table names) on columns in the SELECT list makes it a real pain to troubleshoot the code. I suppose that things like Intellesense make that a bit easier, but that still takes extra time and (for me, anyway), generally gets in the way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

wagner crivelini (10/7/2009)
Hi David
I understand your point.
But it came out so naturally to me writing statements with aliases that it never crossed my mind to say they are optional.
But I should have done it, I have to agree with you on this.
But as some of the guys said here, I feel using alias is more about someone's style than anything else.
I feel using meaningful aliases (and I agree with Jeff's emphasis on the word meaningful) make queries much more readable.
Again someone might argue on what exactly is "meaningful".
In the examples I showed in the article, as there were 3 tables called Table1, Table2 and Table3, I thought it would be meaningful enough to alias them as T1, T2, T3.
But this also might be subject for another discussion ......

For the examples given, the table aliases you used were just fine. Far better to teach table aliases than always using table names or not having any distinguishing marks on columns in the SELECT list at all. So far as telling everyone about proper naming of table aliases every time you post or write an article, just imagine what would happen... to do an article "right", you'd have to explain every good (and maybe, bad practice) that you were using in the article. If Microsoft did that in Books Online, it would be a hundred times longer. Ya just gotta know some stuff. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Heh... of course, I will admit that, no matter which method someone uses to identify the column/table relationships, just about anything would be better than a format like the following (taken from a recent post)...

Set @qrystr = 'Select case: when tbl_UserTypeMaster.UserType = ''Service'' then tbl_RankMaster.RankName+'' ''+tbl_EmpMaster.EmpName + '' ''+ tbl_Empmaster.Surname else

tbl_EmpMaster.EmpName + '' ''+ tbl_Empmaster.Surname +'' ''+ tbl_RankMaster.RankName end ''Name'',tbl_LeaveTypeMaster.LeaveType,tbl_LeaveStatistics.LeaveStatisticsId,

tbl_LeaveStatistics.EmpId, tbl_LeaveStatistics.LeaveTypeId,tbl_LeaveStatistics.FromYear,tbl_LeaveStatistics.ToYear,

(tbl_LeaveStatistics.Granted - isnull(tbl_LeaveStatistics.AlreadyUsed,0) - @Leavedetails) ''NoofDays'',tbl_LeaveStatistics.Granted,isnull(tbl_LeaveStatistics.AlreadyUsed,0) ''AlreadyUsed'',

tbl_LeaveStatistics.CurrentMaxCarry,tbl_LeaveStatistics.CurrentMaxEncash,tbl_LeaveStatistics.ModifiedBy,tbl_LeaveStatistics.ModifiedDate,

tbl_LeaveStatistics.Flag

from

tbl_LeaveStatistics,tbl_leavedetails,tbl_LeaveTypeMaster,tbl_EmpMaster,tbl_UserTypeMaster,tbl_RankMaster

(tbl_LeaveStatistics.Granted - isnull(tbl_LeaveStatistics.AlreadyUsed,0) - @Leavedetails) ''NoofDays''

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

davidpenton SSC Rookie Points: 41 More actions · Answer 10

Jeff Moden (10/7/2009)
Heh... of course, I will admit that, no matter which method someone uses to identify the column/table relationships, just about anything would be better than a format like the following (taken from a recent post)...
...
from
tbl_LeaveStatistics,tbl_leavedetails,tbl_LeaveTypeMaster,tbl_EmpMaster,tbl_UserTypeMaster,tbl_RankMaster

I agree. How about that sickly hungarian notation for database objects? That kind of stuff makes me sad 🙂

Andy DBA SSCommitted Points: 1776 More actions · Answer 11

SuperDBA-207096 (10/7/2009)
Wagner,
You might want to mention in the:
"Excluding the Intersection of the Sets" section this is similar to
select... where not in (select... from table2) but it performs alot better w/ the join vs. where not in?
Just my 02c. Again, well done!
Mark

Sorry, this post isn't about aliases.:-D

Just want to point out that it's best to avoid using NOT IN for "Excluding the Intersection of the Sets" queries if there's a chance your exclusion list (hard coded or sub-select) contains NULLs and if your server's ANSI_NULLS setting is ON. This has been SQL Server's default setting since at least version 7.0 but probably longer. Please search "SET ANSI_NULLS statement" in the BOL index for a complete description of the setting.

Nicole Bowman SSCommitted Points: 1555 More actions · Answer 12

Thank you for a clear and easily understood article. I feel that the use of aliases in the article is actually invaluable for newbies as when I was learning aliases weren't used in the simpler examples and so when I started working on more complex queries I was a bit lost for a while. It is always easier to learn a new skill with simple, easily understood examples and then move to more complex ones. As long as it is pointed out when something is necessary and when it is optional, I think newbies learn better habits faster. I don't always use aliases even with more than one join and this article has made me realise that perhaps I need to clean up my act a bit! Thank you.

Nicole Bowman

Nothing is forever.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Andy DBA (10/7/2009)
SuperDBA-207096 (10/7/2009)
Wagner,
You might want to mention in the:
"Excluding the Intersection of the Sets" section this is similar to
select... where not in (select... from table2) but it performs alot better w/ the join vs. where not in?
Just my 02c. Again, well done!
Mark
Sorry, this post isn't about aliases.:-D
Just want to point out that it's best to avoid using NOT IN for "Excluding the Intersection of the Sets" queries if there's a chance your exclusion list (hard coded or sub-select) contains NULLs and if your server's ANSI_NULLS setting is ON. This has been SQL Server's default setting since at least version 7.0 but probably longer. Please search "SET ANSI_NULLS statement" in the BOL index for a complete description of the setting.

Now, there's a suggestion for a follow up article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andy DBA SSCommitted Points: 1776 More actions · Answer 14

Jeff Moden (10/7/2009)
Now, there's a suggestion for a follow up article.

Sure, hint hint. I think it's old ground, though.

http://www.sqlservercentral.com/blogs/tim_mitchell/archive/2008/05/29/using-in-with-possible-null-values.aspx

http://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/

http://www.sqlservercentral.com/articles/Advanced+Querying/2829/

superlangerkerl Newbie Points: 9 More actions · Answer 15

It was a good article, however these are fundamental types of joins. I would like to have seen something on hash joins and merge joins.