Inner Joins

  • 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 !

  • 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

  • frederico_fonseca - Monday, June 4, 2018 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

    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;

    • Keep table aliases short but meaningful
    • Remove superfluous code (INNER)
    • Alias column name using AliasName = column syntax
    Always a fun topic 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin - Tuesday, June 5, 2018 5:30 AM

    frederico_fonseca - Monday, June 4, 2018 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

    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;

    • 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.

  • 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:

  • jasona.work - Tuesday, June 5, 2018 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:

    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)

  • 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

    with the inner join? Thanks !
  • 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).

  • 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;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 9 posts - 1 through 8 (of 8 total)

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