Exist query Oracle join syntax vs ANSI JOIN syntax

Question

When I write an exist query in ORACLE JOIN syntax like this, it works fine as expected.

Select * from TableA
where not exists (Select 1 from TableB where TableB.Id = TableA.TableBForeignKeyId)

When i write it as ANSI JOIN syntax, it doesn't work.

Select * from TableA
where not exists (Select 1 
                      from (TableA 
                            INNER JOIN TableB 
                            on TableA.TableBForeignKeyId = TableB.Id))

On the ANSI JOIN version, TableA behave not like the upper querys TableA, because of that, the complete query returns no rows.

Not: There is only one row on TableA that has no reference on TableB. This query should return one row of TableA.

Why ANSI JOIN behave like this?

I think you're confused. The first one is **not** a `JOIN` query, so it's neither *Oracle Join* nor *ANSI JOIN*. — Kaushik Nayak, Jul 09 '19 at 06:31
In the second query you reference `TableA` in `FROM` twice. Use aliases to tell which one is in `ON` — Serg, Jul 09 '19 at 06:31
Comma in FROM means cross join with lower precedence than keyword JOINs. You did not "write an exist query in ORACLE JOIN syntax" because there's no comma/join. Why do you think you did? Why do you expect these to be equivalent? We can't explain why things don't act like you expect unless you tell us why you expect these to act the same. — philipxy, Jul 09 '19 at 07:05
@Serg Reusing TableA in the subquery means something & "using aliases"can change what is asked for, so you don't know that that is appropriate. — philipxy, Jul 09 '19 at 07:11
I was thinking that "where TableB.Id = TableA.TableBForeignKeyId" is equal to the INNER JOIN notation. Thats why i think these queries should return the same result. I noticed that the TableA of the inner query is not refer to the outer TableA, i check this with giving alias to the outer TableA and than using this alias in the exist part. If i do this, query works as i expected. But as i say, my mistake was expecting the same result on someField = someOtherField and INNER JOIN notation. — Curious, Jul 09 '19 at 08:27

score 0 · Answer 1 · answered Jul 09 '19 at 06:31

0

The syntax you have written in the first query is good: i.e.

Select * from TableA
where not exists (Select 1 from TableB where TableB.Id = TableA.TableBForeignKeyId)

In the second query, TableA of the inner query will not refer to the outer TableA and hence you are not getting the desired result.

Why you are not happy with the first query?

If you are looking for some other way of writing the query then you can use the following query:

Select * from TableA
where TableA.TableBForeignKeyId not in (Select TableB.Id 
                      from TableB )

Cheers!!

answered Jul 09 '19 at 06:31

Popeye

35,427
4
10
31

Thanks for your comment, I know that TableA of the inner query will not refer to the outer TableA. But i was expecting the opposit. Thats why i ask this question, why it does not refer to the outer TableA – Curious Jul 09 '19 at 08:29
Because in `EXISTS`, if you want to refer outer table then you can directly use the alias. `EXISTS` work on each record of the outer table. Refer good examples of EXISTS: [Link](https://www.oracletutorial.com/oracle-basics/oracle-exists/) – Popeye Jul 09 '19 at 08:45
I also know that, but i dont use alias on my first query and the Table A behaves like the outer TableA. But it doesnt on the inner join query – Curious Jul 09 '19 at 09:50

score 0 · Answer 2 · answered Jul 09 '19 at 11:50

The two queries are not similar at all.

The first is a correlated subquery. The WHERE condition refers to the outer query.

The second is an uncorrelated subquery. There is no connection to the outer query. In this case, the subquery returns a single value, independent of the outer query. If it returns no rows, then the overall query returns no rows. If it returns one or more rows, then the overall query returns multiple rows.

Both are valid, correct syntax. You should use the one you want, which would normally be the correlated subquery.

Exist query Oracle join syntax vs ANSI JOIN syntax

2 Answers2