Changing IS NULL with (+) Syntax

Question

I have this query :

SELECT B.id, B.name, D.id, D.name FROM TBB B, TDD D
WHERE (D.id = B.id OR D.id IS NULL)

From what I thought, (D.id = B.id OR D.id IS NULL) will show record that have id in both table TBB and TDD the but also show all of B.id records even if both table doesn't have the same id because of D.id IS NULL

So, Is this the same one like my above query :

 SELECT B.id, B.name, D.id, D.name FROM TBB B, TDD D
    WHERE B.id = D.id (+)

Thanks in advance!

Yes, it's equivalent. Can't you just run and see that it returns the same resultset ? — Nir Alfasi, Sep 20 '12 at 04:52

solaimuruganv · Answer 1 · 2012-09-20T05:17:41.970

1

second query will return all the row exist in TBB table, not matching column in TDD table would return as NULL.

in-case TDD table have any ID that doesn't match with TBB table ID , that row will not return by above two query.

edited Sep 20 '12 at 05:17

answered Sep 20 '12 at 04:52

solaimuruganv

27,177
1
18
23

for your first query, actually single null values in table TDD will match the each unique row in TBB table. – solaimuruganv Sep 20 '12 at 05:22

score 0 · Answer 2 · edited May 23 '17 at 12:12

I am pretty sure that your top query would result in a cartesian join to every record in B for records that have a NULL due to the or statement in your query.

Based on that, you would be better off using the outer join.

I also wrote a lengthy Q&A that looks at joins and how to pull data from multiple tables you might be interested in ( How can an SQL query return data from multiple tables ) it covers unions, inner and outer joins as well as subqueries. It has loads of code and output results which are explained in detail. (To the point I hit the answer length limit, so had to post a second answer)

Edit: After running a quick test, this is what I come up with:

    mysql> select a.ID, a.Title, b.Name as Author
    from books a join authors b
    on a.authorID=b.ID or b.id=0;

    +----+----------------------+-------------------+
    | ID | Title                | Author            |
    +----+----------------------+-------------------+
    |  1 | Call of the Wild     | Fluffeh           |
    |  1 | Call of the Wild     | Jack London       |
    |  2 | Martin Eden          | Fluffeh           |
    |  2 | Martin Eden          | Jack London       |
    |  3 | Old Goriot           | Fluffeh           |
    |  3 | Old Goriot           | Honore de Balzac  |
    |  4 | Cousin Bette         | Fluffeh           |
    |  4 | Cousin Bette         | Honore de Balzac  |
    |  5 | Jew Suess            | Fluffeh           |
    |  5 | Jew Suess            | Lion Feuchtwanger |
    |  6 | Nana                 | Fluffeh           |
    |  6 | Nana                 | Emile Zola        |
    |  7 | The Belly of Paris   | Fluffeh           |
    |  7 | The Belly of Paris   | Emile Zola        |
    |  8 | In Cold blood        | Fluffeh           |
    |  8 | In Cold blood        | Truman Capote     |
    |  9 | Breakfast at Tiffany | Fluffeh           |
    |  9 | Breakfast at Tiffany | Truman Capote     |
    +----+----------------------+-------------------+
    18 rows in set (0.00 sec)

    mysql> select a.ID, a.Title, b.Name as Author
    from books a right outer join authors b
    on a.authorID=b.ID;
    +------+----------------------+-------------------+
    | ID   | Title                | Author            |
    +------+----------------------+-------------------+
    | NULL | NULL                 | Fluffeh           |
    |    1 | Call of the Wild     | Jack London       |
    |    2 | Martin Eden          | Jack London       |
    |    3 | Old Goriot           | Honore de Balzac  |
    |    4 | Cousin Bette         | Honore de Balzac  |
    |    5 | Jew Suess            | Lion Feuchtwanger |
    |    6 | Nana                 | Emile Zola        |
    |    7 | The Belly of Paris   | Emile Zola        |
    |    8 | In Cold blood        | Truman Capote     |
    |    9 | Breakfast at Tiffany | Truman Capote     |
    +------+----------------------+-------------------+
    10 rows in set (0.00 sec)

Which is certainly not the same as an outer join. As I thought (at least in MySQL) the results cartesian in the first statement, but not in the outer join.

Yes, but becuase there is an OR statement in the `where` clause, the ones in the `D is null` section aren't joined on a particular field and I think it will match every row from D which has the NULL with EVERY row from B. Having said that, what do you get when you run it? — Fluffeh, Sep 20 '12 at 05:02
But I'm using ( ) on my where condition. Isn't that helped to group the where condition? — RedFux227, Sep 20 '12 at 05:06
@RedFux227 See Edit, I added an example. They are not the same. — Fluffeh, Sep 20 '12 at 05:17
@RedFux227 You have nothing outside the `( )` so it makes no difference whether you have them or not. — Fluffeh, Sep 20 '12 at 05:18
What If the syntax like this : Where B.id ='A001' And (B.id = D.id OR D.id is null). Is that will have the same effect like using B.id left join D.id? — RedFux227, Sep 20 '12 at 05:26
@RedFux227 I don't think so, but I am testing it on MySQL (though I use Oracle at work a fair bit). Why don't you run both queries and check the results against the oracle DB? I am curious myself now if Oracle does it different (Though think not) — Fluffeh, Sep 20 '12 at 05:35
Both queries return the same record although its only 1 row in total. I thought that if I put B.id = 'A001' , then the D.id is null will only check against the B.id with 'A001' not all of B.id. Right? PS : both B.id and D.id is primary key — RedFux227, Sep 20 '12 at 06:00
@RedFux227 It might bring back the same result depending on the certain data in the tables, but depending on the data, it will bring back different results. To prove it, throw some extra NULL rows into D and see what it brings back :) — Fluffeh, Sep 20 '12 at 06:12

Changing IS NULL with (+) Syntax

2 Answers2