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

[Chris]

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/

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

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

[John A DAVIS]

left join where item in right table is null
>>> "Chris" <[EMAIL PROTECTED]> 10/3/2007 10:32:01 PM >>>
Aleksandar Vojnovic wrote:> 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 tutorialshttp://www.designmagick.com/-- PHP General Mailing List (http://www.php.net/)To unsubscribe, visit: http://www.php.net/unsub.php

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

[Chris]

Aleksandar Vojnovic wrote:
It seems you missed my point :) if you would need all the data then 
select them all, but if you need only partial data from the table then 
you could limit yourself to that specific columns. I doubt everybody 
need everything all the time. True?


Ahh - you meant the select * from table bit ;) My apologies.

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

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

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

[Aleksandar Vojnovic]

It seems you missed my point :) if you would need all the data then 
select them all, but if you need only partial data from the table then 
you could limit yourself to that specific columns. I doubt everybody 
need everything all the time. True?


Aleksandar

Chris wrote:

Aleksandar Vojnovic wrote:
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?




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

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

[Chris]

Aleksandar Vojnovic wrote:
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 Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

[Ryan Jameson \(USA\)]

You can also do an outer join and look for NULLS in the key (which means
"no match"). In some cases it may be more efficient. <>< Ryan

-Original Message-
From: James Ausmus [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 03, 2007 10:00 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; php-db@lists.php.net
Subject: [PHP-DB] Re: [PHP] RE: the opposite of a join?

On 10/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Hi J,
> >
> >
> > Checkout this,
> >
> >
> > SELECT * FROM tbl_company where id not in (SELECT companyID from
> > tbl_contacts)
> >
>
>
> Brilliant! This is exactly what I was looking for, and is quite 
> logical/readable!  Thanks to everyone for the ideas!
>
> J
>

No, don't do this! It is a very inefficient way to retrieve the
information you are looking for (Use a query analysis tool to check it
out yourself, if you want) - if your tables get to any larger size at
all, it will start having a noticeable performance impact on your script
(not to mention your DB) - let the DB do the hard work and use a LEFT
JOIN syntax, the database can optimize that much more efficiently. Only
if your DB doesn't support the LEFT JOIN syntax would you want to do the
above.

-James



>
>
>
>
>
> >
> >
> >
> >
> > Regards,
> > Lasitha Alawatta
> > Application Developer
> > Destinations of the World Holding Establishment P O Box: 19950 
> > Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 
> > (Ext.)
> > 7 Fax +971 4 295 8910
> > + [EMAIL PROTECTED]
> >
> > -Original Message-
> > From: John Pillion [mailto:[EMAIL PROTECTED] 
> > 
> On Behalf Of
> > [EMAIL PROTECTED]
> > Sent: Wednesday, October 03, 2007 2:21 PM
> > To: [EMAIL PROTECTED]; php-db@lists.php.net
> > Subject: [PHP-DB] the opposite of a join?
> >
> > I have a company table and a contacts table.  In the contacts table,

> > there is a field called "companyID" which is a link to a row in the 
> > company table.
> >
> >
> >
> > What is the easiest way to query the company table for all the 
> > company rows whose ID is NOT linked to in the contact table? 
> > Basically, the opposite of a join?
> >
> >
> >
> > Thanks
> >
> >
> >
> > J
> >
> >
> >
> > DOTW DISCLAIMER:
> >
> > This e-mail and any attachments are strictly confidential and 
> > intended for the addressee only. If you are not the named addressee 
> > you must not >
> disclose, copy or take
> > any action in reliance of this transmission and you should notify us

> > as soon as possible. If you have received it in error, please 
> > contact the message sender immediately.
> > This e-mail and any attachments are believed to be free from viruses

> > but it is your responsibility to carry out all necessary virus 
> > checks and DOTW accepts no liability in connection therewith.
> >
> > This e-mail and all other electronic (including voice) 
> > communications from the sender's company are for informational 
> > purposes only.  No such communication is intended by the sender to 
> > constitute either an electronic record or an electronic signature or

> > to constitute any agreement by the sender to conduct a transaction 
> > by electronic means.
> >
>
> >
>
>

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

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