Databases
StackOverflow2013
Postsdbo
PODoes the order of expressions or predicates make any difference in the join condition for outer joins in standard SQL?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODoes the order of expressions or predicates make any difference in the join condition for outer joins in standard SQL?
    primarykey
    data
    text
    <h2>So I'm having a bit of a frustrating day. I was fired from a contract I had been working the past few weeks because I did not "mesh with the team." Two examples were cited:</h2> <p><strong>1)</strong> Yesterday I was asked to delete all the data from a SQL Server database except for some baseline system data. The requestor (not my boss) had about eight scripts she had used for this task a few months ago. Apparently no one has ever thought to script out the data model or the system data.</p> <p>This person had established a pattern of over-complicating things, so I was trying to understand the reasons for the task and the end goal. I also struggled to understand why she always got all sorts of vague errors that required all these extra scripts to "remove and re-add constraints".</p> <p>I was quite willing to do anything necessary and we talked for several minutes about it. For obvious reasons I thought that seeking to understand why was a good thing. Suddenly she decided that it would just be faster to do it herself rather than spend the time explaining it all to me and I went back to my other work without thinking more about it.</p> <p>Somehow this was interpreted negatively.</p> <p><strong>2)</strong> Several weeks ago during my first week I was asked to work on merging some data from another system. I was given a 600+ line script of SQL snippets that all appeared to be important along with an oral tirade of complaints about poor data and the original programmers. Naturally it took a bit of time to wade through closely but after a few days my final result emerged as a simple MERGE in roughly 25 lines.</p> <p>I've been writing SQL for more than ten years and I consider myself to have some expertise in the area. My coworkers were getting a little impatient as I peeled away the layers of unnecessary complexity and I stayed late to finish the task as promised.</p> <p>I wasn't sure how readily they would accept that the solution of the problem appeared to be exactly one very short command and I wondered if I was missing something that would prove to be embarrassing. Unfortunately all of my probing questions had been repeatedly met with the kind of dismissive non-answers that poor teachers give to an overly-curious elementary student. So I sent the query along in an email and went home.</p> <p>The next morning I was told (same person as #1) that it was all wrong but it was stuff I couldn't be expected to know as the new guy. She explained that she had to restrain herself from rewriting it all. One of her points was something minor about the data that I had already intended to ask her about myself. The other was purely SQL.</p> <h3>(The question starts here.)</h3> <p>Take a typical left outer join scenario. We all know that the order of the tables is quite significant, e.g., <strong>Q1</strong> and <strong>Q2</strong> are not equivalent:</p> <pre><code>SELECT A.x, B.y FROM A LEFT OUTER JOIN B ON A.id = B.id -- (Q1) SELECT A.x, B.y FROM B LEFT OUTER JOIN A ON B.id = A.id -- (Q2) </code></pre> <p>When I think conceptually about multiple joins it usually seems natural to me to imagine picking up the new table as the object of interest and then describing how its rows are related to what's come before. Keeping the terms in parallel doesn't have any advantage to me and by my own habit I generally write the join condition this way:</p> <pre><code>SELECT A.x, B.y FROM A LEFT OUTER JOIN B ON B.id = A.id -- (Q3) </code></pre> <p>So I find myself being tutored about how the order of tables matters in an outer join. I had thought this test of my intelligence has already been done during the job interview. My confusion turned into stupor as I realized she was focusing only on the equality comparison. To her <strong>Q3</strong> was wrong and <strong>Q1</strong> was the version I needed instead. </p> <p>I diplomatically insisted that it did not make any difference at all and that I could easily prove my case if she wanted. I tried to clarify her reasoning but this is the kind of person who doesn't listen closely to your questions no matter how carefully they are phrased or how many technical words you use to suggest a tiny level of competence, so I decided that I just couldn't convince her I'm not an idiot. And I agreed to change the script because it wasn't worth arguing about.</p> <p>I suppose that not immediately swallowing my pride demonstrated that I was not "coachable."</p> <hr> <p>As for style alone, I conform to any standard the employer prefers though their SQL is often sloppy in other ways. I do recognize that with the old-style outer join syntax this would matter. Beyond that I've never heard anyone make this case. Please answer this question and redeem my reputation.</p> <p><em>Does the order of expressions or predicates make any difference in the join condition for outer joins in standard SQL?</em></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USshawnt00
    1. USshawnt00
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODoes the order of expressions or predicates make any difference in the join condition for outer joins in standard SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODoes the order of expressions or predicates make any difference in the join condition for outer joins in standard SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload