SQL Left Join

[wurzel]

Apologies if this is is a very basic question but my SQL is a bit rusty these days.

Simple scenario, I have 2 tables, A & B and I want to return everything form table A that doesn't have a corresponding record in B. So I've tried the followwing 2 statements:

SELECT a.* FROM TableA a
LEFT JOIN TableB b ON a.id = b.id
WHERE b.id IS NULL

SELECT a.* FROM TableA a
LEFT JOIN TableB b ON (a.id = b.id AND b.id IS NULL)

They both return different results. I would have expected the results to be same for both.

What approach is correct?

[Spacecadet]

What approach is correct?

The first one

[TheFaQQer]

What approach is correct?

It depends on what you want

SELECT a.* FROM TableA a
LEFT JOIN TableB b ON a.id = b.id
WHERE b.id IS NULL

SELECT a.* FROM TableA a
LEFT JOIN TableB b ON (a.id = b.id AND b.id IS NULL)

The difference is that the join condition in the second cursor may return no rows (there are no rows in b with a null ID). This means that (a.id=b.id AND b.id IS NULL) essentially means that you return everything in TableA.

SQL > CREATE table CUK1 (id number);

Table created.

Elapsed: 00:00:00.48
SQL > begin
  2    for i in 1..4 loop
  3      insert into cuk1 values (i);
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.46
SQL >
SQL > create table cuk2 (id number);

Table created.

Elapsed: 00:00:00.48
SQL >
SQL > begin
  2    for i in 3..4 loop
  3      insert into cuk2 values (i);
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.48
SQL > select * from cuk1;

        ID
----------
         1
         2
         3
         4

Elapsed: 00:00:00.59
SQL > select * from cuk2;

        ID
----------
         3
         4

Elapsed: 00:00:00.59
SQL > select cuk1.id, cuk2.id
  2  from   cuk1 LEFT OUTER JOIN cuk2 ON (cuk1.id = cuk2.id);

        ID         ID
---------- ----------
         3          3
         4          4
         1
         2

Elapsed: 00:00:00.64
SQL > SELECT a.* FROM cuk1 a
  2  LEFT JOIN cuk2 b ON a.id = b.id
  3  WHERE b.id IS NULL;

        ID
----------
         1
         2

Elapsed: 00:00:00.61
SQL > SELECT a.* FROM cuk1 a
  2  LEFT JOIN cuk2 b ON (a.id = b.id AND b.id IS NULL);

        ID
----------
         1
         2
         3
         4

Elapsed: 00:00:00.60
SQL > SELECT a.*
  2  FROM   cuk1 a
  3  JOIN   cuk2 b ON ( a.id = b.id );

        ID
----------
         3
         4

Elapsed: 00:00:00.62
SQL > SELECT a.* FROM cuk1 a
  2  LEFT JOIN cuk2 b ON a.id = b.id
  3  WHERE b.id IS NOT NULL;

        ID
----------
         3
         4

Elapsed: 00:00:00.59
SQL > drop table cuk1;

Table dropped.

Elapsed: 00:00:00.51
SQL >
SQL > drop table cuk2;

Table dropped.

Elapsed: 00:00:00.57
SQL >

[Spacecadet]

It depends on what you want

I want to return everything form table A that doesn't have a corresponding record in B.

Ahem

[TheFaQQer]

Ahem

Didn't read that bit.

[lilelvis2000]

Wouldn't using a not exists clause work better?

eg: select a* from tableA a
where not exists (select * from TableB b where b.id=a.id)

though

select a* from TableA a left join TableB b on a.id=b.id
where b.id IS NULL

is another way to go.

[wurzel]

It depends on what you want



The difference is that the join condition in the second cursor may return no rows (there are no rows in b with a null ID). This means that (a.id=b.id AND b.id IS NULL) essentially means that you return everything in TableA......

Perfect thanks.

Wouldn't using a not exists clause work better?

eg: select a* from tableA a
where not exists (select * from TableB b where b.id=a.id)

though

select a* from TableA a left join TableB b on a.id=b.id
where b.id IS NULL

is another way to go.

Would one perform better than the other?

[vetran]

Try this, makes sense to me

Coding Horror: A Visual Explanation of SQL Joins

[lilelvis2000]

Perfect thanks.



Would one perform better than the other?

You would need to try it. go on.

[TheFaQQer]

Would one perform better than the other?

It depends on whether the column is nullable or not, and (probably) which database you are using.

In MySQL, for example, left join performs well where both columns are not-nullable. However, it performs badly where one or both of the columns is nullable, in which case an EXISTS / NOT EXISTS would perform better.

You could also use a NOT IN instead of NOT EXISTS, which (I think!) would work best, although if the column is nullable then you would need to include an additional check for nulls.