0

Yesterday, while debugging another developer code, I came across following query (say this query as Q1)

select b.id from b left join a on b.a_id = a.id where a.id is null

Table a has one field:

id        primary key

Table b has two fields:

id        primary key
a_id      foreign key (reference a.id)

When I ran above query (Q1), it gave me results: All b.id where b.a_id was not available in a.id

But I didn't understand above query (Q1), specially "where" clause. If we are checking for a.id is null, how can it match to find those not available in b table.

Also, what is difference between Q1 and Q2 (below query):

select b.id from b where not exists (select * from a where a.id = b.a_id)

I didn't even understand Q2.

Mackan
  • 6,200
  • 2
  • 25
  • 45
I-M-JM
  • 15,732
  • 26
  • 77
  • 103
  • Please note the direction: `b` is `left-joined` with `a`. – PM 77-1 May 07 '15 at 05:44
  • @AbhikChakraborty looks like you posted a wrong link – I-M-JM May 07 '15 at 05:50
  • @I-M-JM sorry here is the link http://stackoverflow.com/questions/29944271/how-does-left-join-is-null-eliminate-records-which-are-there-in-one-table-and/29944479#29944479 – Abhik Chakraborty May 07 '15 at 05:57
  • 1
    Try running `select b.id, b.a_id, a.id from b left join a on a.id = b.a_id` without the where. Note the difference between `b.a_id` and `a.id`. Also try what changes if you replace `left join` by `inner join`, for example. – CompuChip May 07 '15 at 06:05

3 Answers3

1

the key point in Q1 is left join. as you maybe know The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders). a side note: as you mentioned id is primary key of table a so it is not null and the where clause will never be true!!! In Q2 you only select those records which has corresponding values in table a. you can find more information here

Mahmoud
  • 883
  • 7
  • 17
1

The first query, Q1:

I'm sure you will see this again, it's not uncommon. A left join will take all records from the left table, and those from the right that match the reference. But what the where clause is doing is to invert this, so that it will only select where there is no match, i.e. where a.id is null.

You could put it in an even more descriptive text:

  • take all rows from table b
  • take all matched rows from table a (based on PK->FK)
  • now select only the records from b that have no match in a

The second query, Q2, will give you the same results, but it's another way to write it. It's basically:

  • take all rows from table b
  • now select only the records from b where there is no matching record in a (doesn't exist)

Regarding performance of the two queries, you can read more here. It basically boils down to "it depends" though. Normally an exists will have very good performance, but since we're prefixing it with not it still has to check every record.

Edit:
There is a good write up about these queries, and some additional ways to write them, here: What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

Community
  • 1
  • 1
Mackan
  • 6,200
  • 2
  • 25
  • 45
1

Thread Step

First

select b.id from b

This is easy, the result is all the rows in table b.

Second

select b.id from b left join a on b.a_id = a.id

Notice the number of records in this result will be same as the first step, because of left join.To understand left join, you may try inner join:

select b.id from b inner join a on b.a_id = a.id

The number of rows should be less, bacause inner join will only return the row which come from table b and joined to a. So the record with a_id not in table a will not shown. But left join don't do this! Left join will show all the records in table b, even the record it doesn't exists in table a, and the record which could not be joined with table a will leave null.

That's why here comes step three

Three

Add the null check condition

select b.id from b left join a on b.a_id = a.id where a.id is null
huaoguo
  • 127
  • 5