Left outer join equivalence - Oracle vs ANSI syntax

I was reminded last week while visiting on customer site that it's easy to confuse the syntax you should use if you switch between Oracle's syntax and ANSI syntax for outer joins. Here's an example using the good old standby SCOTT schema to show what I mean. First, I'll show the data from both tables we'll use (emp and dept) so the rest of the examples will be easier to follow.

SCOTT@ORA11R2> select * from emp ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SCOTT@ORA11R2> select * from dept ;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

Now, let's join the tables to produce a result set that includes all rows from emp but limits the dept table info to only be included if the deptno = 20.

SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno(+) and d.deptno(+) = 20;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7902 FORD               20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7566 JONES              20 RESEARCH
      7369 SMITH              20 RESEARCH
      7839 KING
      7934 MILLER
      7782 CLARK
      7900 JAMES
      7844 TURNER
      7698 BLAKE
      7654 MARTIN
      7521 WARD
      7499 ALLEN

14 rows selected.

Note that we get back the entire set of 14 rows from emp, but only 5 rows have the deptno and dname column values populated. This is because the filter on deptno(+) = 20 is written so that it is applied before the join between emp and dept occurs. Therefore, only rows with deptno = 20 will be joined to their matching emp rows. The remaining rows for employees who are in the other departments will be returned due to the outer join as no rows from dept will be available to match to.

If we wrote the join without including the (+) on the filter, we'd have gotten back only rows for deptno 20 employees as follows:

SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno(+) and d.deptno = 20;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7369 SMITH              20 RESEARCH
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7902 FORD               20 RESEARCH

5 rows selected.

However, if we want to return rows only for deptno 20, then we shouldn't use an outer join at all. If the filter is applied after the join, then the rows for other departments will have null values in the deptno and dname columns (as shown earlier) and therefore will not match the filter condition and be eliminated from the result set. You can see that the result is the same if you remove the outer join operator.

SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e, dept d
  3  where e.deptno = d.deptno
  4  and d.deptno = 20;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7369 SMITH              20 RESEARCH
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7902 FORD               20 RESEARCH

5 rows selected.

When using ANSI join syntax, you need to make sure to use the filter condition properly as well. With ANSI syntax, the difference is in where you place the condition. In order for the filter to be applied before the join, you must place the filter inside the join condition.

SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e left outer join dept d
  3  on (e.deptno = d.deptno and d.deptno = 20);

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7902 FORD               20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7566 JONES              20 RESEARCH
      7369 SMITH              20 RESEARCH
      7839 KING
      7934 MILLER
      7782 CLARK
      7900 JAMES
      7844 TURNER
      7698 BLAKE
      7654 MARTIN
      7521 WARD
      7499 ALLEN

14 rows selected.

If you place the filter in the WHERE clause, the filter is applied after the join.

SCOTT@ORA11R2> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e left outer join dept d
  3  on (e.deptno = d.deptno)
  4  where d.deptno = 20;

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7369 SMITH              20 RESEARCH
      7566 JONES              20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7902 FORD               20 RESEARCH

5 rows selected.

I tend to use the Oracle syntax most often and am so used to it that when I was shown the ANSI syntax, it took a minute to recall the difference in placement of the filter condition (thanks for the nudge Karl!). It's fairly obvious when looking at a simple example like this as to where the condition belongs, but when you've got a much more complex SQL statement with multiple joins, the effects of placing the filter in the wrong spot may be overlooked.

Hope this little reminder helps!

You know you're a true gadget geek when...

...the power goes off in your home during a major thunder/lightning/rain storm and instead of going to the junk drawer to pull out the flashlight you reach for your iPhone and turn it on so that the soft glow illuminates your surroundings.


I even went so far as to go to the App Store and download a free flashlight app that I then played with until the power came back on. I used my nifty new little app to light my way to the water cooler to refill my water bottle and then returned to the comfort of my couch to play with all the different flashlight choices in the app until the power came back on several minutes later.

Actually it's kinda scary to think that I've crossed some line that makes my iPhone the "go to" solution in almost any situation.

Do not feed...

What exactly am I supposed to think when the park near our house has this sign posted in numerous places:

This cracks me up for at least two reasons:
1) because it doesn't say "Beware of alligators" or anything like that, it simply tells you to not feed alligators or other dangerous critters like cats, raccoons and possums, and
2) because there are enough of all of those animals hanging around the park that a warning (and actually a legal ordinance) is needed!

As I was pondering the sign further (while my daughter played in this danger zone), I was thinking that if all these animals happened to be around at the same time, and the alligator looked hungry, I'd grab one of the others and toss it over to the gator, grab my kiddo and run. So, I wonder if it's OK to do that? I mean, since they're all on the sign is it OK for them to eat each other?

Geesh...I think of weird things. Oh well, eat or be eaten I say!

My favorite parts of today

Every night over dinner my daughter asks me "what was your favorite part of the day Momma"? Well, tonight I had several favorite things to share. My first favorite was that I found an Oracle bug that has to do with subquery factoring (I'm going to blog about this one soon). And my second favorite (well, it's really my *most* favorite) was spending some fun time at the beach with my kiddo. Looks like we had fun doesn't it?!?

I should've been in movies...

Have you seen the movie Avatar? If not, this will just look like a crazily distorted blue photo of me. But, if you saw the movie, you'll see me as a Na'vi. Cool, huh?!?

I loved the movie (it was fabulous in 3D!) and if you haven't seen it yet, check out the trailer.

PowerPoint is the Enemy

I went to Starbucks about an hour ago to treat myself to a mocha (even though it was nonfat). And while waiting for my coffee to arrive I noticed the front page of The New York Times. Right smack in the middle of the page was a horrendous PowerPoint slide and the title of the article underneath read "We Have Met the Enemy and He Is PowerPoint".


Even though I knew I could get the story online when I got home, I just had to buy the paper and read it immediately.

I'm a huge believer in the concept of "death by PowerPoint". There certainly have been a multitude of presentations that I have suffered through that nearly killed me! I follow and attempt to learn from the presentation methods of Garr Reynolds, Nancy Duarte, Seth Godin and others who are out there promoting more effective ways to deliver presentations. While this article's title may paint with broader strokes than I would by saying "PowerPoint is the enemy", I mostly agree with the sentiment. PowerPoint itself may not the enemy, but surely the way it is commonly used is close to a criminal act.

Just look at that slide the article displays! Could you ever make any sense of that thing? It was shown to the General leading the American and NATO forces in Afghanistan last summer and he was quoted as saying, "When we understand that slide, we'll have won the war." Other high ranking military officers said things like "PowerPoint makes us stupid" and "It's dangerous because it can create the illusion of understanding and the illusion of control." One General even went so far as to ban the use of PowerPoint presentations!

In my experience, PowerPoint presentations have replaced meaningful documents (like white papers) and are used in an attempt to distill all the knowledge deemed pertinent on a topic into a few bullet points. The hope is that a busy person (i.e. usually an executive) can "get the idea" of what is being conveyed without having to spend much time reading something more detailed. But, the supposedly concise presentations I see most often have entire slides filled with sentences (all preceded by the infamous bullet point) that are so busily annoying that I can barely stand to look at them. A presenter is really not even a necessary component of the presentation anymore as most people intend for their slide decks to stand alone and tell the story without need for a person to deliver the message verbally. When these presentations are delivered, they are (more often than not) simply read by the presenter. On top of that, many presentations are not verbally delivered by those directly responsible for the content, but instead are delivered by a high level manager who only shows up on the day of the big presentation to lead the show. I don't know...that just seems odd to me. I'm not going to say there are no merits at all to this use of the tool and this particular approach, but I do find that what is delivered is rarely what I personally want to see/hear.

The article states that military commanders say that "slides impart less information than a five-page paper can hold, and that they relieve the briefer of the need to polish writing to convey an analytic, persuasive point." The technical presentations I see suffer from this in that they are not very polished nor persuasive. But even a "bad paper" that makes an attempt to provide detailed information on a topic is better than being bored to death with a slide presentation that either puts too much or too little meaningful content in front of me. But, the fact that the use of PowerPoint has become so commonplace, it is rare to ever get a detailed paper...you only get the slides. And even if you do get a paper, it is often full of grammatical issues and seems to have been written after the slides such that the paper doesn't offer that much more information but only slightly lengthens the bullet points from the slides.

There are a lot of people out there trying to change the way PowerPoint is used. They work to show that presentations can be used to support the speaker and the topic effectively. I think that most of these advocates separate presentation design and use from documents providing detailed information. I just wish there was a faster way to affect change in this area!

What happened to the days of speakers standing alone in front of the crowd? When was the last time you attended a session that didn't use PowerPoint? Maybe we all need to ban the use of PowerPoint (and all presentation software) for a while and return to a more simple way of sharing our message. Perhaps instead of propagating the use of PowerPoint, organizations could start hosting Toastmasters meetings and teach people how to become more effective speakers.

As I said earlier, I don't think it's necessarily the software that's the problem. But, it's sure an easy target on which to place the blame for the sad situation we find ourselves in every time we are forced to sit through another agonizingly boring presentation.

Oh, the places you'll go!

How do you feel about the place you're in, the places you've been, and the places you hope to go? As I was reading "Oh, the Places You'll Go" by Dr. Seuss to my daughter this evening, I was struck by how much wisdom was held in that simple children's story. So, I did a quick search and found the words of the story for you.

For those sports buffs out there, check out this cool video using this story that aired as an opener for the 2006 SuperBowl.



Note (5/8/2010): This post originally included a video link that is no longer available so I removed the reference.

Yes, I'm still here

I admit it. I'm a binge blogger (I borrowed this term from a friend of mine who posted on the same topic). I make several blog entries over the course of a week or so and then it may be a month or two before I show up again. I suppose my good intentions of blogging regularly just get swept under the rug of day-to-day reality and how/where I choose to spend my time. But, regardless of frequency, I suppose it's fairly obvious from this post that I'm still here.

I was doing a bit of blog surfing tonight to catch up on a long list of blogs I enjoy reading and came across the following from a recent Seth Godin post:

"Firing the customers you can't possibly please gives you the bandwidth and resources to coddle the ones that truly deserve your attention and repay you with referrals, applause and loyalty."

Amen! This reminded me a a time early in my days as a self-employed consultant back in the mid-90's. I took on a client that turned out to be a royal pain-in-the-backside. They'd call me to complain about their fax not working (among other odd things) and even though the services I had contracted with them to provide had nothing to do with most of the things they called to rant about, they expected me to address and fix them all. I ended up giving them back the initial fee they'd paid and canceled the contract. I felt better immediately and the time I was spending dealing with them was quickly filled with several new (and much nicer and "normal") customers.

So, I can relate to this quote. I also think it just makes good sense across the board. Whatever you give your time and attention to (careers, relationships, etc) should yield positive (whatever positive means to you) results. If you find that you're always unhappy when dealing with certain aspects of life, then I think it's entirely reasonable to look for ways to eliminate/reduce whatever it is and replace it with something that brings more happiness and positiveness to you. It may seem difficult and a bit scary to do so, but I think in the long run it's the way to go.

On a different note, I spoke today at the MAOP (Mid Atlantic Association of Oracle Professionals) Conference held today in Reston VA. I really enjoyed being there and got some good feedback and questions after speaking. In my current work I don't get the opportunity to speak in front of folks like I used to when I taught classes at least a couple of weeks a month. So, I'm really grateful to MAOP for asking me to speak and for having the opportunity to get a "fix" from presenting to a great audience!

And speaking of conferences, the annual Hotsos Symposium is coming up March 7-11 in Dallas. This will be the first one I've missed since they started having them 8 years ago (bummer!). But, if you want to attend one of the best conferences anywhere, and the only one focused specifically on Oracle performance, there's still seats available. I highly recommend it!

2000 columns - take 2

Thanks to everyone who commented on yesterday's post. After Randolf's comment about the 1000 column limit, I realized that so far, I've not attempted to create the full table in one go. Instead, I've been building 20-ish separate tables that contain the needed raw data plus the computed/aggregated columns. This speaks to Noons comment about having separate tables that access and group data up that can be executed in multiple streams

The SAS guys want to be able to access a single row that represents all the information they need for one type of data (for example, a shipment). So far, as I build the separate tables, I've been using a couple of views that combine several of the tables. The SAS guys then use the view to build a SAS dataset and merge multiple datasets together.

Yesterday's post was prompted by the prospect of having to build a single table in Oracle that does what they're currently doing via SAS. And to Joel's point, SAS is certainly not Oracle! One of the original goals was to ultimately have a table built and maintained in Oracle and only accessed by SAS, not multiple objects in Oracle that have to be merged into SAS datasets.

I've been so busy and focused on getting the data formulated/aggregated, and doing so as efficiently as possible, that I hadn't even made the attempt to build out a full table with all the columns of all the tables included in that one table. When I started thinking about it yesterday, I got all freaked out about doing it and that's what prompted my post. I hadn't even thought about the Oracle imposed limits on the number of columns! I've been told more than once that the team had delivered 1000s of columns before, so I just hadn't given it much thought. But, given there is an Oracle limit, I'm guessing they've never delivered a final Oracle table like they want to do this time...so, it's either been done in SAS previously or the Oracle tables didn't exceed the 1000 column limit.

So, I've got lots to figure out over the next few weeks. This just may be more fun than I can stand! :)

2000 columns

How many columns does the largest table you've ever worked with contain? The current project I'm working on has 1 table with almost 2000 columns (and it's likely to add more!). This is the most highly denormalized design I've ever encountered and there's something about it that makes the performance optimizer in me cringe. But, the statisticians that have to munch and crunch this data in SAS tell me this format best suits their needs (based on similar designs used successfully in previous projects).

I think I'm really more concerned about the work that has to be done to populate these columns as most of the columns contain aggregations or formulations of some sort or another. So, perhaps it's not the number of columns that really is niggling at me as it is everything that must occur to produce the values contained in a single row (it's a lot).

What's your experience? Did the number of columns help, hinder or make no difference in the design and performance of the application that used such wide tables? This phase of the project is a proof of concept so it'll be a while before I get hard data on how well (or not) this design really works, but it has certainly made me curious about what other folks have experienced.

Let me hear from you if you have any comments or insights!