Greetings!

I have a problem that it seems would best be solved using
subqueries. However, I am working on a server that is running MySQL
3.23.58, so subqueries are not available to me.

What I am trying to do:
I have two tables, each containing approximately 37,000 records. I
want to compare the index field in Table A to the index field in
Table B. I would like to see all records where the index in Table A
does not exist in Table B.

Thoughts? Suggestions?

Much appreciated!

-Aaron

Aaron

An exclusion join:

SELECT a.col
FROM a
LEFT JOIN b ON a.col=b.col
WHERE b.col IS NULL;

PB

Aaron Fischer wrote:
> Greetings!
>
> I have a problem that it seems would best be solved using subqueries.
> However, I am working on a server that is running MySQL 3.23.58, so
> subqueries are not available to me.
>
> What I am trying to do:
> I have two tables, each containing approximately 37,000 records. I
> want to compare the index field in Table A to the index field in Table
> B. I would like to see all records where the index in Table A does
> not exist in Table B.
>
> Thoughts? Suggestions?
>
> Much appreciated!
>
> -Aaron
>
>

Aaron Fischer wrote:
> Greetings!
>
> I have a problem that it seems would best be solved using subqueries.
> However, I am working on a server that is running MySQL 3.23.58, so
> subqueries are not available to me.
>
> What I am trying to do:
> I have two tables, each containing approximately 37,000 records. I want
> to compare the index field in Table A to the index field in Table B. I
> would like to see all records where the index in Table A does not exist
> in Table B.

Use an "exclusion join:"

SELECT ...
FROM tblA
LEFT OUTER JOIN tblB ON col=col
WHERE tblB.col IS NULL;

Baron

Thanks Peter and Baron, these both worked well.

The "left join on" took .1919 seconds and the "left outer join as"
took .1780 seconds.

=)


On Oct 26, 2007, at 11:37 AM, Peter Brawley wrote:

> Aaron
>
> An exclusion join:
>
> SELECT a.col
> FROM a
> LEFT JOIN b ON a.col=b.col
> WHERE b.col IS NULL;
>
> PB
>

Aaron Fischer wrote:
> Greetings!
>
> I have a problem that it seems would best be solved using subqueries.
> However, I am working on a server that is running MySQL 3.23.58, so
> subqueries are not available to me.
>
> What I am trying to do:
> I have two tables, each containing approximately 37,000 records. I
> want to compare the index field in Table A to the index field in Table
> B. I would like to see all records where the index in Table A does not
> exist in Table B.
>

You want to select from A, not from B, yes?

SELECT a.* FROM foo AS a
LEFT OUTER JOIN bar AS b
ON a.index_field = b.index_field
WHERE b.index_field IS NULL;

brian

Aaron Fischer wrote:
> Thanks Peter and Baron, these both worked well.
>
> The "left join on" took .1919 seconds and the "left outer join as" took
> .1780 seconds.

They are synonymous in MySQL. The only difference is 6 extra characters
in the query text. The difference was probably due to caches.

Baron

On 10/26/07, Baron Schwartz <baron@xaprb.com> wrote:
>
> Aaron Fischer wrote:
> > Thanks Peter and Baron, these both worked well.
> >
> > The "left join on" took .1919 seconds and the "left outer join as" took
> > .1780 seconds.
>
> They are synonymous in MySQL. The only difference is 6 extra characters
> in the query text. The difference was probably due to caches.
>
> Baron
>
> Out of curiosity which cache are you referring to?

3.23 does not have query cache. Are you referring to key, table, or some
other cache? (or all of the above?)

I would think that the key and table cache would not be effected by the
addition of an optional word that does not affect how the query is
processed...

Where is my understanding incorrect or incomplete?

Aaron

>The "left join on" took .1919 seconds and the "left outer join as"
took .1780 seconds.

Caching

PB

-----

Aaron Fischer wrote:
> Thanks Peter and Baron, these both worked well.
>
> The "left join on" took .1919 seconds and the "left outer join as"
> took .1780 seconds.
>
> =)
>
>
> On Oct 26, 2007, at 11:37 AM, Peter Brawley wrote:
>
>> Aaron
>>
>> An exclusion join:
>>
>> SELECT a.col
>> FROM a
>> LEFT JOIN b ON a.col=b.col
>> WHERE b.col IS NULL;
>>
>> PB
>>
>
>

Rob Wultsch wrote:
> On 10/26/07, Baron Schwartz <baron@xaprb.com> wrote:
>> Aaron Fischer wrote:
>>> Thanks Peter and Baron, these both worked well.
>>>
>>> The "left join on" took .1919 seconds and the "left outer join as" took
>>> .1780 seconds.
>> They are synonymous in MySQL. The only difference is 6 extra characters
>> in the query text. The difference was probably due to caches.
>>
>> Baron
>>
>> Out of curiosity which cache are you referring to?
>
> 3.23 does not have query cache. Are you referring to key, table, or some
> other cache? (or all of the above?)
>
> I would think that the key and table cache would not be effected by the
> addition of an optional word that does not affect how the query is
> processed...
>
> Where is my understanding incorrect or incomplete?

I'm referring to the operating system's caches. Really such a small
difference isn't significant anyway -- who knows what was happening on
the server at that time. But running a query, then running it again,
will often be at least a tiny bit faster the second time because the
data has been read from disk into the OS caches.

The query cache (in newer versions) wouldn't because the queries
aren't byte-for-byte identical.

If one did a proper benchmark on these two queries and found any
difference at all aside from "six extra bytes", I'd be very surprised.