June 4, 2018 at 4:17 pm
Hi Friends,
I was wondering is there a Rule of Thumb when I am joining my tables as it relates to the result that I want? For example
should the ON clause always be followed by the parent table and Foreign Key= Primary Key. Thanks !
June 4, 2018 at 4:33 pm
That falls under the category of standards (company) and where none exist personal preference.
On my case standards (and personal preference) are
- tables always aliased
- select, from and inner/left/full join aligned left
- On keyword on separate line, indented 3 spaces
and/or aligned with ON keyword (or further indented with multiple conditions
- select Columns on a list mode, comma on the left side with a space before alias name, columns aligned on alias name
- join columns on joined table index order if possible
No right or wrong here - just be consistent and use a format that is easy to read and maintain.
select ob1.name
, ix1.name
, ic1.index_column_id
from sys.objects ob1
inner join sys.indexes ix1
on ix1.object_id = ob1.object_id
inner join sys.index_columns ic1
on ic1.object_id = ix1.object_id
and ic1.index_id = ix1.index_id
June 5, 2018 at 5:30 am
frederico_fonseca - Monday, June 4, 2018 4:33 PMThat falls under the category of standards (company) and where none exist personal preference.On my case standards (and personal preference) are
- tables always aliased
- select, from and inner/left/full join aligned left
- On keyword on separate line, indented 3 spaces
and/or aligned with ON keyword (or further indented with multiple conditions
- select Columns on a list mode, comma on the left side with a space before alias name, columns aligned on alias name
- join columns on joined table index order if possibleNo right or wrong here - just be consistent and use a format that is easy to read and maintain.
select ob1.name
, ix1.name
, ic1.index_column_id
from sys.objects ob1
inner join sys.indexes ix1
on ix1.object_id = ob1.object_id
inner join sys.index_columns ic1
on ic1.object_id = ix1.object_id
and ic1.index_id = ix1.index_id
Just to illustrate how different those standards may be, here is my version of the same query:
SELECT
ObjName = o.name
, IxName = ix.name
, IxColId = ic.index_column_id
FROM
sys.objects o
JOIN sys.indexes ix ON ix.object_id = o.object_id
JOIN sys.index_columns ic ON ic.object_id = ix.object_id
AND ic.index_id = ix.index_id;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 5, 2018 at 9:12 am
Phil Parkin - Tuesday, June 5, 2018 5:30 AMfrederico_fonseca - Monday, June 4, 2018 4:33 PMThat falls under the category of standards (company) and where none exist personal preference.On my case standards (and personal preference) are
- tables always aliased
- select, from and inner/left/full join aligned left
- On keyword on separate line, indented 3 spaces
and/or aligned with ON keyword (or further indented with multiple conditions
- select Columns on a list mode, comma on the left side with a space before alias name, columns aligned on alias name
- join columns on joined table index order if possibleNo right or wrong here - just be consistent and use a format that is easy to read and maintain.
select ob1.name
, ix1.name
, ic1.index_column_id
from sys.objects ob1
inner join sys.indexes ix1
on ix1.object_id = ob1.object_id
inner join sys.index_columns ic1
on ic1.object_id = ix1.object_id
and ic1.index_id = ix1.index_idJust to illustrate how different those standards may be, here is my version of the same query:
SELECT
ObjName = o.name
, IxName = ix.name
, IxColId = ic.index_column_id
FROM
sys.objects o
JOIN sys.indexes ix ON ix.object_id = o.object_id
JOIN sys.index_columns ic ON ic.object_id = ix.object_id
AND ic.index_id = ix.index_id;
- Keep table aliases short but meaningful
- Remove superfluous code (INNER)
- Alias column name using AliasName = column syntax
Always a fun topic 🙂
And again, personal preference. I insist on using INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER when specifying joins.
June 5, 2018 at 10:23 am
I was told it runs faster when it's written like this:select ob1.name, ix1.name, ic1.index_column_id from sys.objects ob1 inner join sys.indexes ix1 on ix1.object_id = ob1.object_id inner join sys.index_columns ic1 on ic1.object_id = ix1.object_id and ic1.index_id = ix1.index_id
(YES I kid!):hehe:
June 5, 2018 at 1:56 pm
jasona.work - Tuesday, June 5, 2018 10:23 AMI was told it runs faster when it's written like this:select ob1.name, ix1.name, ic1.index_column_id from sys.objects ob1 inner join sys.indexes ix1 on ix1.object_id = ob1.object_id inner join sys.index_columns ic1 on ic1.object_id = ix1.object_id and ic1.index_id = ix1.index_id(YES I kid!):hehe:
Yep. At warp 10. Now see what you can do to "Make it so!"
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 5, 2018 at 4:19 pm
I was just wondering what method are you using as far as which tables to join with the tables you chose. And why did you use the AND clause
June 6, 2018 at 2:39 am
In terms of which tables to join on, either it's because you need data from that table or because the existence of a row in a table (or existence with certain conditions) is necessary to filter the result set in some way. In the example code the indexes table joins to the objects table by object_id so that we have a collection of all objects with their associated indexes. The second join then extends that by adding information about which columns are part of that index, which requires two pieces of information - that it's part of the same index and that it's part of the same overall object (you might actually get away without the object_id reference but it's often a good idea to fully specify relationships as it can help the query engine pick an optimal plan).
June 6, 2018 at 6:31 am
And I usually work on an opposite way
SELECT
o.name AS ObjName,
ix.name AS IxName ,
ic.index_column_id AS IxColId
FROM sys.objects o
JOIN sys.indexes ix ON o.object_id = ix.object_id
JOIN sys.index_columns ic ON ix.object_id = ic.object_id
AND ix.index_id = ic.index_id;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply