A Refresher on Joins

  • Jambu Krishnamurthy

    SSCrazy

    Points: 2054

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjambu/2937.asp

  • Jens Meyer-Beeck

    SSC Enthusiast

    Points: 158

    What you wanted to show is a so-called exception join. But this is not a good way:

    select * from t1

    where t1.f1 not in (select t2.f1 from t2)

    Exception joins should better be implemented like this:

    select * from t1

    where not exists (select 1 from t2 where t1.f1 = t2.f1)

    cheers

    jens

  • Bojidar Alexandrov

    Old Hand

    Points: 378

    Better way is:

    SELECT *

    FROM t1 a

    LEFT JOIN t2 b ON a.f1=b.f1

    WHERE b.f1 IS NULL

  • Bojidar Alexandrov

    Old Hand

    Points: 378

    Good article, little bit simple and short but its a refresher anyway

  • Gerard van de Ven

    SSC Veteran

    Points: 217

    Rather simple article. It covers the basics, but not much more than that. I think I would have made some concrete tables from the start, to make the examples a bit more readable.

    Would also have been interesting to have multiple table joins and then discuss performance issues that come with that. That would have made things interesting for more than the very basic T-SQL coder.

  • Richard Tew-243440

    SSC Enthusiast

    Points: 138

    Good, quick, basic.

    Just what you need for newbies

    Can I use it for a course I am teaching?

  • John Morrison-401734

    SSC Journeyman

    Points: 87

    "I am not sure of a real business need for a Cartesian product"

    I have seen a pretty useful implementation of a Cartesian product.

    Have you ever had a report where you need a series of data grouped by a specific criteria in the series in the data (foreign keys), then by Days, weeks, or months. But when ever there was no data for the day week or month it would simply be missing from your report? Well that is fixed by creating a pretty simple static table to store your dates in. Then with a cross join on your series and your dates you will get a product all of the dates for each of your series even though they are unrelated you still get use out of their product. Eg

    Table IDs

    ID
    1
    2
    4

    Dates

    date
    2007-04-01 00:00:00.000
    2007-04-02 00:00:00.000
    2007-04-03 00:00:00.000

    You can now cross join (Select * from IDs Cross Join Dates)

    1 2007-04-01 00:00:00.000
    1 2007-04-02 00:00:00.000
    1 2007-04-03 00:00:00.000
    2 2007-04-01 00:00:00.000
    2 2007-04-02 00:00:00.000
    2 2007-04-03 00:00:00.000
    4 2007-04-01 00:00:00.000
    4 2007-04-02 00:00:00.000
    4 2007-04-03 00:00:00.000

     

    now that you have this nice neat Cartesian product you can include a left join to the table with your meat and get your statistics.

    Select IDs.ID, Dates.Date, IsNull(Count(meat.id), 0) from IDs Cross Join Dates Left Join meat on meat.id = ids.ID and meat.date = dates.date Group by IDs.ID, Dates.Date

    It works out pretty nice when aggregating data for 3 dimensional charting apps.

    Try it out

    Select

    1 as ID into #ids

    insert

    into #ids values (2)

    insert

    into #ids values (3)

    Select

    '2007-04-01 00:00:00.000' as date into #dates

    insert

    into #dates values ('2007-04-02 00:00:00.000')

    insert

    into #dates values ('2007-04-03 00:00:00.000')

    Select

    1 as id, '2007-04-03 00:00:00.000' as date into #meat

    insert

    into #meat (id, date) values (1,'2007-04-03 00:00:00.000')

    insert

    into #meat (id, date) values (1,'2007-04-03 00:00:00.000')

    insert

    into #meat (id, date) values (1,'2007-04-01 00:00:00.000')

    insert

    into #meat (id, date) values (2,'2007-04-03 00:00:00.000')

    insert

    into #meat (id, date) values (2,'2007-04-03 00:00:00.000')

    insert

    into #meat (id, date) values (2,'2007-04-02 00:00:00.000')

    insert

    into #meat (id, date) values (2,'2007-04-02 00:00:00.000')

    insert

    into #meat (id, date) values (3,'2007-04-01 00:00:00.000')

    Select

    #ids.id, #dates.date, isnull(count(#meat.id),0) as ID_Date_Count

    from

    #ids cross join #dates

    Left join #meat on #meat.id = #ids.id and #meat.date = #dates.date

    Group

    by #ids.id, #dates.date

    Drop

    table #ids

    Drop

    table #date

    Drop

    table #meat

     

    Sorry for the bad markup but im not just #'n meat this is pretty useful.

  • Confucius247

    SSC-Addicted

    Points: 424

    Nonsense, how's this good for newbies? It immediately starts using 'join' and ',' without explaining these are short-hand notations, the layout of the actual SQL is bad and the table names are stupid.

    It doesn't discuss basic concepts that I would expect on a 'refresher' e.g. multiple criteria in on clauses, nested selects, etc.

    Also, where he says 'Observe the query and output carefully...and then see if you can get the following output, which you will agree makes more sense. '

    How does it make sense to randomly lop off the last two rows?

  • Jens Meyer-Beeck

    SSC Enthusiast

    Points: 158

    Hi Bojidar,

    why do you claim this would be better:

    SELECT *

    FROM t1 a

    LEFT JOIN t2 b ON a.f1=b.f1

    WHERE b.f1 IS NULL

    You didn't give any proof. Did you examine what the optimizer does with it?

    My opinion: No good SQL. You should better reconsider this

    cheers

    jens

  • jcb

    SSCertifiable

    Points: 6574

    Dude,
    this join ´ll work work fine for most instances, but put a row in T2 where F1 is null and u ll got a real problem.
    select * from t1where t1.f1 not in (select t2.f1 from t2)
    I suggest it
    select * from t1where t1.f1 not in (select t2.f1 from t2 AND T2.F1 IS NOT NULL)
    Bojidar,
    I try to avoid outer joins because at least one table is full scanned. Not good for joining two big tables with workable indexes. For performance i guess the NOT EXISTS solutions above wins, but i can be wrong. Try two or more ways to do thing with a eye in the execution plan can be instructive. =)
  • Craig A. Silvis

    Ten Centuries

    Points: 1038

    I found this to be an informative and helpful refresher, thanks!

     

  • Charles Kincaid

    SSChampion

    Points: 13593

    Good refresher.

    Our company policy is to NOT use table aliases.  The VP in charge of development came down hard.  You have to use them in self joins though.

    ATBCharles Kincaid

  • Gemma Davies

    SSC Veteran

    Points: 269

    I'm still confused with them.  I never know when to use which, I just experiment!

  • Ram-425798

    Right there with Babe

    Points: 748

    Mr. Jambu Krishnamurthy!

     

    Excellent article.

    Regards,

    Ramaswamy

  • Dennis Wells

    Grasshopper

    Points: 17

    Mr. Jambu

    As a newbee, I enjoyed the Article, however when I attempted to get the results for the last example on the FULL OUTER JOINS, I am unable to obtain the results you are showing, please provide me with the necessary joins to accompolish the example, Thanks

    Observe the query and output carefully...and then see if you can get the following output, which you will agree makes more sense.

    t1.f1 t2.f1 t3.f1===== ===== =====6     6     67     7     NULL8     8     NULL9     9     NULL10    10    NULLNULL  11    NULLNULL  12    12NULL  13    13NULL  14    NULLNULL  15    NULL5     NULL  NULL4     NULL  NULL3     NULL  32     NULL  21     NULL  NULL

Viewing 15 posts - 1 through 15 (of 25 total)

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