2 Replies Latest reply on Oct 2, 2006 5:39 AM by Dan Bracuk

    Join method and writing

    Level 7
      When learning joins in SQL I learned to write a join like this:

      Select t.id, t.date, t.status, t.client, t.open, t.signature, t.details,
      p.title
      FROM Tickets T LEFT OUTER JOIN Project P
      ON t.project_id = p.project_id
      Where t.dirname = '#arguments.user#' AND t.open = 1
      ORDER BY t.date DESC

      Now that I work for a company, I noticed that they write their joins like
      this:

      Select t.id, t.date, t.status, t.client, t.open, t.signature, t.details,
      p.title
      FROM Tickets T, Project P
      Where t.project_id = p.project_id AND t.dirname = '#arguments.user#' AND
      t.open = 1
      ORDER BY t.date DESC

      Which is better and why?
      --
      Wally Kolcz
      Developer / Support


        • 1. Re: Join method and writing
          Level 7
          Performance-wise, I don't *think* there's much or any difference.

          But my personal position is that it's a lot clearer what's going on if you
          have your table-join statements separated from your dataset-filtering
          statements, rather than chucking them all in together. They are doing two
          different things, after all.

          Your own example demonstrates a difference too: the two queries are not
          equivalent, and return two different recordsets. Because your first query
          is a LEFT OUTER join, the second one is an INNER join. Whilst it is
          possible to code a left outer join with WHERE clauses, it's ugly and
          clunky.

          I think the usual difference is that people are talk the WHERE method in DB
          101, and they might not have progressed onto DB 102 and had to get their
          brain around joins. Or they are the sort of person who think "well I
          already know a way that works, why should I 'waste my time' learning
          another way?"

          In my shop it's written into the coding standard that one must use JOIN
          statements; using a WHERE statement instead would earn you a (small)
          reprimand.

          --
          Adam
          • 2. Re: Join method and writing
            Dan Bracuk Level 5
            They mean different things and will return different results. Also remember that the s in sql stands for structured, not standard. Syntax is db specific. Your first example would not work in Oracle, versions 8 and below.