Oracle (+) Notation – How to Use Within Functions

join;oracle

If we use (+) notation we can have outer join. For example:

select *
from tab_a, tab_b
where tab_a.num = tab_b.num(+)

Is outer join.
If we use function around columns, for example:

select *
from tab_a, tab_b
where round(tab_a.num, 2) = round(tab_b.num(+), 2)

Is it still outer join? Or is it becomes inner join?

Best Answer

Is it still outer join? Or is it becomes inner join?

It is still an outer join:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table tab_a(num integer);
create table tab_b(num integer);

insert into tab_a(num) values(1);
insert into tab_a(num) values(2);

insert into tab_b(num) values(2);
insert into tab_b(num) values(3);

Query 1:

select *
from tab_a, tab_b
where tab_a.num = tab_b.num(+)

Results:

| NUM |    NUM |
|-----|--------|
|   2 |      2 |
|   1 | (null) |

Query 2:

select *
from tab_a, tab_b
where round(tab_a.num, 2) = round(tab_b.num(+), 2)

Results:

| NUM |    NUM |
|-----|--------|
|   2 |      2 |
|   1 | (null) |

Related Solutions

Sql-server – Looking for a snippet illustrating ambiguous result of a join in a where clause

This can only be demonstrated on databases set to 80 compatibility level (or SQL Server <= 2000).

CREATE TABLE dbo.splunge(splunge_id INT);

CREATE TABLE dbo.mort(splunge_id INT, name VARCHAR(32));

INSERT dbo.splunge(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT dbo.mort(splunge_id, name) SELECT 1,'hi' UNION SELECT 2,NULL;

-- returns all three rows - should it?
SELECT * 
  FROM dbo.splunge AS s, dbo.mort AS m
  WHERE s.splunge_id *= m.splunge_id
  AND m.name IS NULL;

-- returns rows 2 and 3
SELECT * 
  FROM dbo.splunge AS s
  LEFT OUTER JOIN dbo.mort AS m
  ON s.splunge_id = m.splunge_id
  WHERE m.name IS NULL;

DROP TABLE dbo.splunge, dbo.mort;

Don't want to use a nullable name column to demonstrate? Ok, how about a nullable foreign key column?

CREATE TABLE dbo.splunge(splunge_id INT);

CREATE TABLE dbo.mort(splunge_id INT);

INSERT dbo.splunge(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT dbo.mort(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT NULL;

-- returns all three rows - should it?
SELECT * 
  FROM dbo.splunge AS s, dbo.mort AS m
  WHERE s.splunge_id *= m.splunge_id
  AND m.splunge_id IS NULL;

-- only returns row 3 (because it was the only row truly missing)
SELECT * 
  FROM dbo.splunge AS s
  LEFT OUTER JOIN dbo.mort AS m
  ON s.splunge_id = m.splunge_id
  WHERE m.splunge_id IS NULL;

DROP TABLE dbo.splunge, dbo.mort;

Not sure why you need to demonstrate this today - you just simply shouldn't be using *= / =* joins at all, regardless of whether you can reproduce some ambiguous behavior - they are deprecated and won't work in any database that doesn't support 80 compatibility level. And if you are using compatibility level 80, you need to be prepared for a ton of other functional differences and potential breaking changes when you do finally upgrade.

Sql-server – Single value from a list of comma separated values when used in inner join in SQL Server

mdbo and @ are hints it's MS Sql Server. Use a splitter table valued function of choice, Jeff Moden's one http://www.sqlservercentral.com/articles/Tally+Table/72993/ performs very good. So

select * 
from Test.dbo.Test1 a
   INNER JOIN  @TestTable T on T.Name in (select item from [DelimitedSplit8K](a.Name, ','))

But may I say the best way to solve the problem is to have no such a problem. Any chance Test.dbo.Test1 could be refactored to have a separate row for every item from Name column? Then take this opportunity.

it's possible to use plain like, but note it prevents optimizer from using any index. In the above version at least index on T.Name can be used.

select * 
    from Test.dbo.Test1 a
       INNER JOIN  @TestTable T on ','+a.Name+',' like '%,'+T.name+',%'

Best Answer

Related Solutions

Sql-server – Looking for a snippet illustrating ambiguous result of a join in a where clause

Sql-server – Single value from a list of comma separated values when used in inner join in SQL Server

Related Question