SQL question.

azith28

Hi,

I'm taking an SQL course for work. I currently am a Database Programmer, we just dont use SQL, so I'm occasionally having some confusion linking the thought process of one language to another...but todays lesson has me...bamboozled..


Customer LEFT INNER JOIN Orders;

vs

Orders RIGHT INNER JOIN Customers;

This does the same damn thing. Why the fuck would a language (That i was under the impression was one of the best and most optimized for databases) make two different keywords that do the same thing just depending on which statement you type first or last? I understand what the command is doing, it just seems like its such a STUPID idea that I'm actually feeling angry about it.

While I'm ranting. I'm learning that SQL is far from Standardized. Why would an Oracle database for instance not accept the same keywords (such as AS) as SQL Developer etc.

bowen

Oracle is unique enough that it has corporate inertia. It can do pretty much whatever it wants because it is the big player on campus.

Cisco is like this too. They don't follow standards because fuck you.

However, they are the same, but different. right/left is just telling what table to implicitly include the nulls on there, in order to be feature complete, they wanted to give people the option, to pick and choose which one there. There's really no difference, it's all about preference. Just like there's not really a whole lot of difference between while and for loops in programming, they can essentially be used the same way, because you're testing for boolean truths.

The added bonus is with right/left on inner and outer joins is that it lets you change result sets drastically without changing a whole lot to the rest of your DML statement.

hsu

For an inner join, right vs left doesn't matter. Inner is a more specific keyword and overrides the right and left keyword.

Right and left are only valid for outer joins or regular joins.

You have the option of both right and left because you can have more than 2 joins, so sometimes you want to do both a right join and a left join in the same statement, like "a left outer join b right outer join c". Notice how this statement cannot be re-written to use only left outer joins or only right outer joins. While you should rewrite to use only left joins (my preference) or only right joins whenever possible, sometimes it's unavoidable.

schuss

SQL sometimes hews to order of operations, thus the example of right vs. left doing the same thing (generally, people will only use one or the other, I only use left)

As far as DB specific-SQL statements - this is a thing, and it sort of sucks, but also allows some DB makers to make advanced features available that others don't have.

If you want to see really f'd up, start looking at the query plans around different statements, especially when ? wildcards are involved being passed from a front-end. That said, SQL is incredibly straightforward and easy compared to other types of programming, and provides a ton of power.

Justforspite

Hi azith28. Joins are probably the most confusing part of SQL. I am by no means an expert on SQL but I do need to use it when I develop to interact with the back end. This page has a pretty good diagram to explain it ( on page 2 of the article) http://www.sql-join.com/sql-join-types My go to reference for all things SQL is http://www.w3schools.com/sql/ Hope that helps!