4 ms·

Contrary to articles conclusion, there are databases (i.e. Oracle) where it doesn’t matter how you write joins, but oracle also has support for left/right/full

Contrary to articles conclusion, there are databases (i.e. Oracle) where it doesn’t matter how you write joins, but oracle also has support for left/right/full outer joins in the where clause Left join ... is the same as leftcol=rightcol(+) (oracle has special syntax for this with that + sign). In summary, depending on database, it may not matter how you write these joins, but it’s best to stick to SQL standard using JOIN if you’d like cross database support.

irrational 8y ago
I have noticed that many authors of SQL articles don't seem to be aware of Oracles quirks. For example, recently I read an article about NULLs in SQL. The author didn't specify any particular database, but wrote as if what he was saying applied to all relational databases. But Oracle treats empty strings and NULLs as the same thing, which I don't think the article's author was aware of.
- RHSeeger 8y ago
  > treats empty strings and NULLs as the same thing Wow, that's ridiculous. They are very clearly two different things. For example "this has no value" and "we don't know the value of this".
  - paulddraper 8y ago
    More like "this value is empty text" and "this has no value/is unknown". I agree though. Makes as much sense as treating 0 and NULL as the same.
    - kijin 8y ago
      What does ANSI SQL say about the matter? If Oracle's behavior is non-standard, it makes sense for the aforementioned author to ignore it.
      - irrational 8y ago
        My understanding is that Oracle's behavior existed prior to the standard. That is, Oracle came first, then the standard was written years later. Oracle hasn't changed to match the standard because of issues with backwards compliance.
- panarky 8y ago
  "this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls" https://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF30037 https://docs.oracle.com/database/121/SQLRF/sql_elements005.h...
  - mcguire 8y ago
    How are we supposed to treat them differently? Oracle's the one putting the damn nulls in the db. (Recently tripped over this one.)
  - Foobar8568 8y ago
    They finally did a 180 on that bullshit after years trying to convinced everyone that empty string = null. (marketing, blogs, oracle ace etc.)
- Svip 8y ago
  Indeed, Sybase for instance treats a single space (e.g. ' ') the same as an empty string (e.g. ''). For the purpose of concatenating in Sybase, NULL is '' (and thus ' '). So `SELECT a = "a" + NULL` would yield a = "a". But so would `SELECT a = "a" + " "`. MSSQL allows this behaviour, since it's based on Sybase, but you have to explicitly turn it on.
- lukaseder 8y ago
  > I have noticed that many authors of SQL articles don't seem to be aware of Oracles quirks I've spent much more time with Oracle than with other RDBMS. So, what made you think so? > The author didn't specify any particular database, but wrote as if what he was saying applied to all relational databases Yes, in this article's case, the article and conclusion applies to all relational databases. > But Oracle treats empty strings and NULLs as the same thing, which I don't think the article's author was aware of. I am aware of this, but how is it related to the article?
lukaseder 8y ago
Oracle's (+) syntax does not allow for full outer joins. The conclusion would be the same with Oracle's syntax. It's just more obscure, as it would turn into a "fa.film_id < 10" vs "fa.film_id(+) < 10" discussion.

irrational 8y ago

I have noticed that many authors of SQL articles don't seem to be aware of Oracles quirks. For example, recently I read an article about NULLs in SQL. The author didn't specify any particular database, but wrote as if what he was saying applied to all relational databases. But Oracle treats empty strings and NULLs as the same thing, which I don't think the article's author was aware of.

RHSeeger 8y ago
> treats empty strings and NULLs as the same thing Wow, that's ridiculous. They are very clearly two different things. For example "this has no value" and "we don't know the value of this".
- paulddraper 8y ago
  More like "this value is empty text" and "this has no value/is unknown". I agree though. Makes as much sense as treating 0 and NULL as the same.
  - kijin 8y ago
    What does ANSI SQL say about the matter? If Oracle's behavior is non-standard, it makes sense for the aforementioned author to ignore it.
    - irrational 8y ago
      My understanding is that Oracle's behavior existed prior to the standard. That is, Oracle came first, then the standard was written years later. Oracle hasn't changed to match the standard because of issues with backwards compliance.
panarky 8y ago
"this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls" https://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF30037 https://docs.oracle.com/database/121/SQLRF/sql_elements005.h...
- mcguire 8y ago
  How are we supposed to treat them differently? Oracle's the one putting the damn nulls in the db. (Recently tripped over this one.)
- Foobar8568 8y ago
  They finally did a 180 on that bullshit after years trying to convinced everyone that empty string = null. (marketing, blogs, oracle ace etc.)
Svip 8y ago
Indeed, Sybase for instance treats a single space (e.g. ' ') the same as an empty string (e.g. ''). For the purpose of concatenating in Sybase, NULL is '' (and thus ' '). So `SELECT a = "a" + NULL` would yield a = "a". But so would `SELECT a = "a" + " "`. MSSQL allows this behaviour, since it's based on Sybase, but you have to explicitly turn it on.
lukaseder 8y ago
> I have noticed that many authors of SQL articles don't seem to be aware of Oracles quirks I've spent much more time with Oracle than with other RDBMS. So, what made you think so? > The author didn't specify any particular database, but wrote as if what he was saying applied to all relational databases Yes, in this article's case, the article and conclusion applies to all relational databases. > But Oracle treats empty strings and NULLs as the same thing, which I don't think the article's author was aware of. I am aware of this, but how is it related to the article?

lukaseder 8y ago

Oracle's (+) syntax does not allow for full outer joins. The conclusion would be the same with Oracle's syntax. It's just more obscure, as it would turn into a "fa.film_id < 10" vs "fa.film_id(+) < 10" discussion.