PHP DB - [PHP] Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?

Author

[PHP] Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?

John A DAVIS

2007-10-04, 6:59 pm

left join where item in right table is null

Aleksandar Vojnovic wrote:
[color=darkred]
> I would also suggest to limit yourself to things you actually need not
> to select the whole table.

In this case you can't because you're looking for records that exist in
one table that don't exist in another.

Apart from looking at the whole table in each case how else would you do
that?

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Chris

2007-10-04, 9:59 pm

John A DAVIS wrote:
> left join where item in right table is null

That's still going to look at all records in both tables:

1) so it can work out if there is a match from table 1 to table 2
2) so it can then remember to display any records that don't have a match

I was thinking more that if you have something like this:

select * from table1 where id not in (select id from table2);

The db might take that and turn it into:

select * from table1 where id not in (id1,id2,id3);

But it doesn't really matter.

Either way you end up with full table or index scans (depending on the
db and engine you are using if mysql) of both table1 and table2.

myisam tables might just be able to use an index to do this sort of
work, innodb will have to do a table scan because it's mvcc (as will
postgres and others).

--
Postgresql & php tutorials
http://www.designmagick.com/

Sponsored Links