comparing two tables

Captain Paralytic wrote:
> Cleverbum wrote:
>
> > strawberry wrote:
> > > Cleverbum wrote:
> > >
> > > > Forgive me if you think the subject is wrong, but I can't quite think
> > > > how to describe my question.
> > > > I just deleted some rows of a table and need to see if there were any
> > > > values in another table linked to the rows I deleted.
> > > > I'm looking for a query that would perform:
> > > > Show all records from table1 where the value in column `album` is not
> > > > contained in the any of the rows in column `uid` of table2
> > > > If that makes no sense let me know and I'll try to re-word it and write
> > > > an example.
> > >
> > > You'll probably want a query along the these lines (untested):
> > >
> > > SELECT t1.primary_key_field,t2.primary_key_field FROM t1 LEFT JOIN t2
> > > ON t2.foreign_key_field = t1.primary_key_field WHERE
> > > ISNULL(t2.primary_key_field)
> > >
> >
> > This appears to return all permutations of the two tables, which is one
> > heck of a lot!
> >
> > an example:
> > Table one
> > col1 -- col2 -- col3
> > 1 -- one -- blah
> > 2 -- two -- blah
> > 3 -- three -- blah
> >
> > Table two
> > col1 -- col2
> > two -- 2
> > one -- 1
> >
> > What I would like to do is execute a query telling me that there is no
> > row in table two column one containing 'three' even though three is
> > contained in one of the rows of table one column 2
>
> Well the following query does that:
>
> SELECT DISTINCT t1.col1
> FROM `table_one` t1
> LEFT JOIN `table_two` t2 ON t1.col2 = t2.col1
> WHERE t2.col1 IS NULL
>
> Which is pretty much what Strawberry suggested.

Not a slur, an observation. Coming out of big Iron
into the world of MySQL I see a lot of constructs
like this, when standard, and simpler SQL can
achieve the same result.

The above probably does work, but it's not very
obvious what it is doing, unless preceded by
structured English documentation.

I have come across code in the last few years
that have LEFT, INNER and OUTER joins,
all mixed up in multiple SELECT statements,
which made no sense at all. Truly, a 100 line
SQL statement is a little horrible.

Mark Addinall.

addinall wrote:

> Captain Paralytic wrote:
> > Cleverbum wrote:
> >
> > > strawberry wrote:
> > > > Cleverbum wrote:
> > > >
> > > > > Forgive me if you think the subject is wrong, but I can't quite think
> > > > > how to describe my question.
> > > > > I just deleted some rows of a table and need to see if there were any
> > > > > values in another table linked to the rows I deleted.
> > > > > I'm looking for a query that would perform:
> > > > > Show all records from table1 where the value in column `album` is not
> > > > > contained in the any of the rows in column `uid` of table2
> > > > > If that makes no sense let me know and I'll try to re-word it and write
> > > > > an example.
> > > >
> > > > You'll probably want a query along the these lines (untested):
> > > >
> > > > SELECT t1.primary_key_field,t2.primary_key_field FROM t1 LEFT JOIN t2
> > > > ON t2.foreign_key_field = t1.primary_key_field WHERE
> > > > ISNULL(t2.primary_key_field)
> > > >
> > >
> > > This appears to return all permutations of the two tables, which is one
> > > heck of a lot!
> > >
> > > an example:
> > > Table one
> > > col1 -- col2 -- col3
> > > 1 -- one -- blah
> > > 2 -- two -- blah
> > > 3 -- three -- blah
> > >
> > > Table two
> > > col1 -- col2
> > > two -- 2
> > > one -- 1
> > >
> > > What I would like to do is execute a query telling me that there is no
> > > row in table two column one containing 'three' even though three is
> > > contained in one of the rows of table one column 2
> >
> > Well the following query does that:
> >
> > SELECT DISTINCT t1.col1
> > FROM `table_one` t1
> > LEFT JOIN `table_two` t2 ON t1.col2 = t2.col1
> > WHERE t2.col1 IS NULL
> >
> > Which is pretty much what Strawberry suggested.
>
> Not a slur, an observation. Coming out of big Iron
> into the world of MySQL I see a lot of constructs
> like this, when standard, and simpler SQL can
> achieve the same result.
>
> The above probably does work, but it's not very
> obvious what it is doing, unless preceded by
> structured English documentation.
>
> I have come across code in the last few years
> that have LEFT, INNER and OUTER joins,
> all mixed up in multiple SELECT statements,
> which made no sense at all. Truly, a 100 line
> SQL statement is a little horrible.
>
> Mark Addinall.

But using the above approach is several orders of magnitude more
efficient than the sub-select, especially if there are appropriate
indexes built.

Captain Paralytic wrote:
> addinall wrote:
>
> > Captain Paralytic wrote:
> > > Cleverbum wrote:
> > >
> > > > strawberry wrote:
> > > > > Cleverbum wrote:
> > > > >
> > > > > > Forgive me if you think the subject is wrong, but I can't quite think
> > > > > > how to describe my question.
> > > > > > I just deleted some rows of a table and need to see if there were any
> > > > > > values in another table linked to the rows I deleted.
> > > > > > I'm looking for a query that would perform:
> > > > > > Show all records from table1 where the value in column `album` is not
> > > > > > contained in the any of the rows in column `uid` of table2
> > > > > > If that makes no sense let me know and I'll try to re-word it and write
> > > > > > an example.
> > > > >
> > > > > You'll probably want a query along the these lines (untested):
> > > > >
> > > > > SELECT t1.primary_key_field,t2.primary_key_field FROM t1 LEFT JOIN t2
> > > > > ON t2.foreign_key_field = t1.primary_key_field WHERE
> > > > > ISNULL(t2.primary_key_field)
> > > > >
> > > >
> > > > This appears to return all permutations of the two tables, which is one
> > > > heck of a lot!
> > > >
> > > > an example:
> > > > Table one
> > > > col1 -- col2 -- col3
> > > > 1 -- one -- blah
> > > > 2 -- two -- blah
> > > > 3 -- three -- blah
> > > >
> > > > Table two
> > > > col1 -- col2
> > > > two -- 2
> > > > one -- 1
> > > >
> > > > What I would like to do is execute a query telling me that there is no
> > > > row in table two column one containing 'three' even though three is
> > > > contained in one of the rows of table one column 2
> > >
> > > Well the following query does that:
> > >
> > > SELECT DISTINCT t1.col1
> > > FROM `table_one` t1
> > > LEFT JOIN `table_two` t2 ON t1.col2 = t2.col1
> > > WHERE t2.col1 IS NULL
> > >
> > > Which is pretty much what Strawberry suggested.
> >
> > Not a slur, an observation. Coming out of big Iron
> > into the world of MySQL I see a lot of constructs
> > like this, when standard, and simpler SQL can
> > achieve the same result.
> >
> > The above probably does work, but it's not very
> > obvious what it is doing, unless preceded by
> > structured English documentation.
> >
> > I have come across code in the last few years
> > that have LEFT, INNER and OUTER joins,
> > all mixed up in multiple SELECT statements,
> > which made no sense at all. Truly, a 100 line
> > SQL statement is a little horrible.
> >
> > Mark Addinall.
>
> But using the above approach is several orders of magnitude more
> efficient than the sub-select, especially if there are appropriate
> indexes built.

I would suggest that "several orders of magnitude" may have to
be quantified a little more thoroughly in my world. The sub select
works
just as well if you have your indeces built in line with the type of
queries one may want to run. SQL profiling can help you overcome
bottlenecks, examine your plan first and see what it is trying
to do, rather than assuming it is doing what you want it to do!

I'd also like to hire coders that can sit down in front of code
and go "Oh yeah, that's what it is doing", and start work.
Rather than "SHIT! WHAT is THIS!", we need to re-write
it from scratch boss.

I am a firm believer in defining the query using relational
algebra, then turning it into code, keeping the structure
of the problem within the logical construct of the query.

Then tweak it. Views and Cursors are available to most
SQL engines.

Cheers,
Mark Addinall.

addinall wrote:

> Captain Paralytic wrote:
> > addinall wrote:
> >
> > > Captain Paralytic wrote:
> > > > Cleverbum wrote:
> > > >
> > > > > strawberry wrote:
> > > > > > Cleverbum wrote:
> > > > > >
> > > > > > > Forgive me if you think the subject is wrong, but I can't quite think
> > > > > > > how to describe my question.
> > > > > > > I just deleted some rows of a table and need to see if there were any
> > > > > > > values in another table linked to the rows I deleted.
> > > > > > > I'm looking for a query that would perform:
> > > > > > > Show all records from table1 where the value in column `album` is not
> > > > > > > contained in the any of the rows in column `uid` of table2
> > > > > > > If that makes no sense let me know and I'll try to re-word it and write
> > > > > > > an example.
> > > > > >
> > > > > > You'll probably want a query along the these lines (untested):
> > > > > >
> > > > > > SELECT t1.primary_key_field,t2.primary_key_field FROM t1 LEFT JOIN t2
> > > > > > ON t2.foreign_key_field = t1.primary_key_field WHERE
> > > > > > ISNULL(t2.primary_key_field)
> > > > > >
> > > > >
> > > > > This appears to return all permutations of the two tables, which is one
> > > > > heck of a lot!
> > > > >
> > > > > an example:
> > > > > Table one
> > > > > col1 -- col2 -- col3
> > > > > 1 -- one -- blah
> > > > > 2 -- two -- blah
> > > > > 3 -- three -- blah
> > > > >
> > > > > Table two
> > > > > col1 -- col2
> > > > > two -- 2
> > > > > one -- 1
> > > > >
> > > > > What I would like to do is execute a query telling me that there is no
> > > > > row in table two column one containing 'three' even though three is
> > > > > contained in one of the rows of table one column 2
> > > >
> > > > Well the following query does that:
> > > >
> > > > SELECT DISTINCT t1.col1
> > > > FROM `table_one` t1
> > > > LEFT JOIN `table_two` t2 ON t1.col2 = t2.col1
> > > > WHERE t2.col1 IS NULL
> > > >
> > > > Which is pretty much what Strawberry suggested.
> > >
> > > Not a slur, an observation. Coming out of big Iron
> > > into the world of MySQL I see a lot of constructs
> > > like this, when standard, and simpler SQL can
> > > achieve the same result.
> > >
> > > The above probably does work, but it's not very
> > > obvious what it is doing, unless preceded by
> > > structured English documentation.
> > >
> > > I have come across code in the last few years
> > > that have LEFT, INNER and OUTER joins,
> > > all mixed up in multiple SELECT statements,
> > > which made no sense at all. Truly, a 100 line
> > > SQL statement is a little horrible.
> > >
> > > Mark Addinall.
> >
> > But using the above approach is several orders of magnitude more
> > efficient than the sub-select, especially if there are appropriate
> > indexes built.
>
> I would suggest that "several orders of magnitude" may have to
> be quantified a little more thoroughly in my world.
That's fine, but this is a newsgroup. I have tested sub-selects and he
left join method wrt this sort of problem for large databases in MySQL.
I am not going to spend time putting together all the numbers to
publish here, but the difference was very noticeable. Indeed I have
posted this as a solution to others on forums where they were asking
for more efficient ways of doing this sort of task, due to the amount
of time the sub-select method was taking.

> The sub select works
> just as well if you have your indeces built in line with the type of
> queries one may want to run.
I would suggest that the above statement may have to be quantified a
little more thoroughly in my world.
You may find that using EXISTS and NOT EXISTS works better than a
sub-select.

> I'd also like to hire coders that can sit down in front of code
> and go "Oh yeah, that's what it is doing", and start work.
> Rather than "SHIT! WHAT is THIS!", we need to re-write
> it from scratch boss.
Me too, I stated with DB2 in the days when it did not support JOIN or
LEFT JOIN as keywords. If I needed a LEFT OUTER JOIN I had to construct
it using a UNION of two opposing queries. I have also spent many years
with VSAM where all relations have to be coded by hand. I would expect
any coders that I hired to have passed an interview where they had
convinced me that they had the necessary intelligence and experience.

Once the various LEFT JOIN constructs are understood, they can be
extremely powerful tools.

Captain Paralytic wrote:
> addinall wrote:
>
> > Captain Paralytic wrote:
> > > addinall wrote:
> > >
> > > > Captain Paralytic wrote:
> > > > > Cleverbum wrote:
> > > > >
> > > > > > strawberry wrote:
> > > > > > > Cleverbum wrote:
> > > > > > >
> > > > > > > > Forgive me if you think the subject is wrong, but I can't quite think
> > > > > > > > how to describe my question.
> > > > > > > > I just deleted some rows of a table and need to see if there were any
> > > > > > > > values in another table linked to the rows I deleted.
> > > > > > > > I'm looking for a query that would perform:
> > > > > > > > Show all records from table1 where the value in column `album` is not
> > > > > > > > contained in the any of the rows in column `uid` of table2
> > > > > > > > If that makes no sense let me know and I'll try to re-word it and write
> > > > > > > > an example.
> > > > > > >
> > > > > > > You'll probably want a query along the these lines (untested):
> > > > > > >
> > > > > > > SELECT t1.primary_key_field,t2.primary_key_field FROM t1 LEFT JOIN t2
> > > > > > > ON t2.foreign_key_field = t1.primary_key_field WHERE
> > > > > > > ISNULL(t2.primary_key_field)
> > > > > > >
> > > > > >
> > > > > > This appears to return all permutations of the two tables, which is one
> > > > > > heck of a lot!
> > > > > >
> > > > > > an example:
> > > > > > Table one
> > > > > > col1 -- col2 -- col3
> > > > > > 1 -- one -- blah
> > > > > > 2 -- two -- blah
> > > > > > 3 -- three -- blah
> > > > > >
> > > > > > Table two
> > > > > > col1 -- col2
> > > > > > two -- 2
> > > > > > one -- 1
> > > > > >
> > > > > > What I would like to do is execute a query telling me that there is no
> > > > > > row in table two column one containing 'three' even though three is
> > > > > > contained in one of the rows of table one column 2
> > > > >
> > > > > Well the following query does that:
> > > > >
> > > > > SELECT DISTINCT t1.col1
> > > > > FROM `table_one` t1
> > > > > LEFT JOIN `table_two` t2 ON t1.col2 = t2.col1
> > > > > WHERE t2.col1 IS NULL
> > > > >
> > > > > Which is pretty much what Strawberry suggested.
> > > >
> > > > Not a slur, an observation. Coming out of big Iron
> > > > into the world of MySQL I see a lot of constructs
> > > > like this, when standard, and simpler SQL can
> > > > achieve the same result.
> > > >
> > > > The above probably does work, but it's not very
> > > > obvious what it is doing, unless preceded by
> > > > structured English documentation.
> > > >
> > > > I have come across code in the last few years
> > > > that have LEFT, INNER and OUTER joins,
> > > > all mixed up in multiple SELECT statements,
> > > > which made no sense at all. Truly, a 100 line
> > > > SQL statement is a little horrible.
> > > >
> > > > Mark Addinall.
> > >
> > > But using the above approach is several orders of magnitude more
> > > efficient than the sub-select, especially if there are appropriate
> > > indexes built.
> >
> > I would suggest that "several orders of magnitude" may have to
> > be quantified a little more thoroughly in my world.

> That's fine, but this is a newsgroup.

Really? Gosh, I've stumbled into USENET without knowing it!


> I have tested sub-selects and he
> left join method wrt this sort of problem for large databases in MySQL.

How large? I just finished a little while ago with the Australian
Bureau of Statistics. Quite large data sets.

> I am not going to spend time putting together all the numbers to
> publish here, but the difference was very noticeable.

So I can take your word for it?

> Indeed I have
> posted this as a solution to others on forums where they were asking
> for more efficient ways of doing this sort of task, due to the amount
> of time the sub-select method was taking.
>
> > The sub select works
> > just as well if you have your indeces built in line with the type of
> > queries one may want to run.

> I would suggest that the above statement may have to be quantified a
> little more thoroughly in my world.
> You may find that using EXISTS and NOT EXISTS works better than a
> sub-select.

If you spent the time, you may have noticed that my code
relied on NOT EXISTS in the explicit or implied union...

>
> > I'd also like to hire coders that can sit down in front of code
> > and go "Oh yeah, that's what it is doing", and start work.
> > Rather than "SHIT! WHAT is THIS!", we need to re-write
> > it from scratch boss.

> Me too, I stated with DB2 in the days when it did not support JOIN or
> LEFT JOIN as keywords. If I needed a LEFT OUTER JOIN I had to construct
> it using a UNION of two opposing queries. I have also spent many years
> with VSAM where all relations have to be coded by hand.

Not much SQL in VSAM nor VTAM from memory.
Lots of CLIST.


> I would expect
> any coders that I hired to have passed an interview where they had
> convinced me that they had the necessary intelligence and experience.

To undestand bad code? Fair enough. I like my coders
to be productive.

>
> Once the various LEFT JOIN constructs are understood, they can be
> extremely powerful tools.

As are awk(), sed() and any regex. Not easy to read.

Mark Addinall//./\/n$/\

Captain Paralytic wrote:
> Cleverbum wrote:
>
> > addinall wrote:
> > > Cleverbum wrote:
> > > > Forgive me if you think the subject is wrong, but I can't quite think
> > > > how to describe my question.
> > > > I just deleted some rows of a table and need to see if there were any
> > > > values in another table linked to the rows I deleted.
> > > > I'm looking for a query that would perform:
> > > > Show all records from table1 where the value in column `album` is not
> > > > contained in the any of the rows in column `uid` of table2
> > > > If that makes no sense let me know and I'll try to re-word it and write
> > > > an example.
> > >
> > > select album from table_1
> > > union
> > > select uid from table_2
> > > where
> > > uid not in
> > > (select album from table_1)
> > >
> > > I think that seems to be what you require.
> > >
> > > If not, sorry! I mis-understood, or I'm just plain
> > > stupid!
> > >
> > > Mark Addinall.
> >
> > That seems closer, what actually seems to do the trick is:
> >
> > SELECT *
> > FROM personal_images
> > WHERE album NOT
> > IN (
> >
> > SELECT uid
> > FROM personal_albums
> > )
>
> This query will produce exactly the asme results as Strawberry's LEFT
> JOIN compare with NULL query.
>
> The difference is that the LEFT join is much much much more efficient,
> especially if there is an appropriate index.
>
> See http://dev.mysql.com/doc/refman/5.0/...ubqueries.html

I'll bare that in mind, I couldn't understand the left join one as well
so found it harder to modify when it came up with an error first time.
Thanks for all your help.
Martin.