Thursday, September 15, 2005

Joins explained

Remember to print out the following definitions and keep them by your desk in case you forget what a complex right inner join is:

Don't use joins
Joins become a problem with Oracle and SQL Server DBMSs because the two systems have fundamentally different approaches to the concept. Basically, joins don’t always work the same on every DBMS, and you can end up with unexpected result sets. Before we talk about the differences and how to get around them, you should understand the basic types of join clauses:
  • Join
    Joins are powerful SQL commands for creating tables from data that is retrieved from multiple sources.
  • Equi-Join
    Equi-Joins are formed by retrieving all the data from two separate sources and combining it into one, large table.
  • Inner/Outer Join
    Inner Joins are joined on the inner columns of two tables. Outer Joins are joined on the outer columns of two tables.
  • Left/Right Join
    Left Joins are joined on the left columns of two tables. Right Joins are joined on the right columns of two tables.
  • Compound/Complex Joins
    There are also other kinds of joins—left/inner, left/outer, right/inner, and right/outer.
The important thing to remember about joins is to not use them if you want your application to work well with different database servers.

I love the way that it's exactly the kind of thing you would make up, if you were 12 and had absolutely no idea how to answer a homework question.

While I'm at it I can't help quoting a bit more, this time about the well-known ADD command:

ANSI SQL: CREATE, DROP, ADD, UPDATE, DELETE, INSERT, SELECT
As a rule, the only commands you should use are:

  • CREATE and DROP for Data Definition Language (DDL).
  • ADD, UPDATE, DELETE and INSERT for Data Manipulation Language (DML).
  • SELECT for data retrieval.
From the article "Database design for platform independence", TechRepublic 2002.

9 comments:

OraWTFGuy said...

I just want to know how on earth we can join the central columns of a table. There's no such thing, apparently, as a complex two-to-the-left-of-centre join. No wonder the author remained anonymous. There's not that many "experts" prepared to put their names to tripe...

William Robertson said...

Exactly - what if the table has an odd number of columns?

It says "Guest Contributor" at the top, but further down it credits a Sanders Kaufman, who could keep us in WTFs for years. Check out "Reasons to move your .NET network from Oracle to SQL Server" (the links at the bottom of the page are fun too).

Jurij said...

William - but that's just it! You *need an odd number* of columns in a table in order to perform a central join (which BTW, has proowen to be the most effective join of them all!). And who in his right mind would ever create a table with even number of columns, I ask you? ;-)

Yes, I know, sometimes it realy gets boring to count the columns when you design a table, just to be sure it has an odd number. I think the RDBMS vendors should provide some automatism to stick an additional dummy column when you falsely create an even-number-columns table. They just need to be sure that this dummy column does not become the inner nor the outer column, and certanly not leftmost or rightmost column. If they stick it exactly in the center it would be perfect. Central joins would fly that way.

Noons said...

From the Kaufman article mentioned:

"Local and external applications
To migrate applications, follow these steps:

1. Plug in a new SQL Server.
2. Create “devices” and build tables for use by the applications.
3. Take the application offline by disallowing applications’ access to it.
4. Copy the current data from Oracle to SQL Server.
5. Point all the applications at the new database.
6. Allow applications access to the new data tables and devices.
"

Cripes! Someone stop this guy before he causes a national disaster...

William Robertson said...

Those steps remind me of the old Monty Python sketch about the children's TV show where they explain how to cure all known diseases, and it went something like "Study very very hard and become a very famous, brilliant doctor. Then invent some medicine that will make everyone better, and make sure all the sick people can have some so they can all get well. Next week, we'll be showing you how to make box girder bridges..."

James Padfield said...

It's a shame that Oracle doesn't implement the ANSI proposal for an "ALL" join wherein all columns from one table are joined to all columns of another table, a strategy particularly useful (and ensuring maximum selectivity) when you want to retrieve other columns from the same row in the same table as the row you are currently querying.

While this thread (and indeed the forum as a whole) constitutes an amusing aside it is important to remember that the notion that a professional would stoop to simply fabricating information as a means of self-promotion is patently absurd and would certainly never happen - especially not in Oracle circles.

OraWTFGuy said...

it is important to remember that the notion that a professional would stoop to simply fabricating information as a means of self-promotion is patently absurd and would certainly never happen - especially not in Oracle circles.

It simply couldn't happen in the Oracle community. There's too much professional integrity to suggest that anyone would make stuff up that wasn't tested, validated or correct... Nope. Never.

FuzzyPig said...

Datatypes is another corker! "REAL" is available in all DBs?

CREATE TABLE only specifies varchar! Well you ain't getting loose in my DB, if all your data is going to be stored in varchars!

It states from a supposed professional web developer that IE and Navigator are the most popular browsers, this was written in 2002. I thought Navigator died sometime around 99, I'm sure Mozilla was started around then from Navigator remains?

Naphta1914 said...

The Kaufman article has been immortalized by Cris Date in a scathing footnote to "SQL and Relational Theory" (I have the second edition).

Date doesn't mention Kaufman's name, but quotes the article sufficiently that it can be located via an internet search.