Professional Web Applications Themes

comparing two tables - MySQL

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....

  1. #1

    Default comparing two tables

    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.

    Cleverbum Guest

  2. #2

    Default Re: comparing two tables


    Cleverbum wrote:
     

    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_fiels WHERE
    ISNULL(t2.primary_key_field)

    strawberry Guest

  3. #3

    Default Re: comparing two tables


    Cleverbum wrote:
     

    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)

    Apologies if I pressed send twice!

    strawberry Guest

  4. #4

    Default Re: comparing two tables


    strawberry wrote:
     
    >
    > 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_fiels WHERE
    > ISNULL(t2.primary_key_field)[/ref]

    Except that there is no point having t2.primary_key_field in the select
    list if the WHERE clause is ISNULL(t2.primary_key_field) as it will
    always be NULL!

    Captain Guest

  5. #5

    Default Re: comparing two tables


    strawberry wrote: 
    >
    > 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)
    >[/ref]

    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

    Cleverbum Guest

  6. #6

    Default Re: comparing two tables


    Cleverbum wrote: 

    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.

    addinall Guest

  7. #7

    Default Re: comparing two tables


    addinall wrote: 
    >
    > 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.[/ref]

    That seems closer, what actually seems to do the trick is:

    SELECT *
    FROM personal_images
    WHERE album NOT
    IN (

    SELECT uid
    FROM personal_albums
    )

    Cleverbum Guest

  8. #8

    Default Re: comparing two tables


    Cleverbum wrote:
     
    > >
    > > 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)
    > >[/ref]
    >
    > 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[/ref]

    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.

    Captain Guest

  9. #9

    Default Re: comparing two tables


    Cleverbum wrote: 
    > >
    > > 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.[/ref]
    >
    > That seems closer, what actually seems to do the trick is:
    >
    > SELECT *
    > FROM personal_images
    > WHERE album NOT
    > IN (
    >
    > SELECT uid
    > FROM personal_albums
    > )[/ref]

    Yeah. Implied unions are fine. I'm an ORACLE head,
    but I always take SQL back to relational algreba or
    relational calculus if I have a squirmy query.

    Not all SQL engines give us the choice of
    implied unions or joins!

    In that case, depending on your indices;

    select %ROWNUM, album from personal_images
    where
    album not in
    (select uid from personal_albums)

    Depending on the size and structure of your
    database and schema, you could chuck in
    some %HINTS%. Cuts down on your temp
    tables, and the time to store them.


    I'm glad you got it.

    Mark Addinall.

    addinall Guest

  10. #10

    Default Re: comparing two tables


    Cleverbum wrote:
     
    > >
    > > 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.[/ref]
    >
    > That seems closer, what actually seems to do the trick is:
    >
    > SELECT *
    > FROM personal_images
    > WHERE album NOT
    > IN (
    >
    > SELECT uid
    > FROM personal_albums
    > )[/ref]

    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/en/rewriting-subqueries.html

    Captain Guest

  11. #11

    Default Re: comparing two tables


    Captain Paralytic wrote: 
    > >
    > > 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[/ref]
    >
    > 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.[/ref]

    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 doentation.

    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 Guest

  12. #12

    Default Re: comparing two tables


    addinall wrote:
     
    > >
    > > 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.[/ref]
    >
    > 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 doentation.
    >
    > 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.[/ref]

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

    Captain Guest

  13. #13

    Default Re: comparing two tables


    Captain Paralytic wrote: 
    > >
    > > 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 doentation.
    > >
    > > 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.[/ref]
    >
    > But using the above approach is several orders of magnitude more
    > efficient than the sub-select, especially if there are appropriate
    > indexes built.[/ref]

    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 "! 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 Guest

  14. #14

    Default Re: comparing two tables


    addinall wrote:
     
    > >
    > > But using the above approach is several orders of magnitude more
    > > efficient than the sub-select, especially if there are appropriate
    > > indexes built.[/ref]
    >
    > I would suggest that "several orders of magnitude" may have to
    > be quantified a little more thoroughly in my world.[/ref]
    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.
     
    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.
     
    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 Guest

  15. #15

    Default Re: comparing two tables


    Captain Paralytic wrote: 
    > >
    > > I would suggest that "several orders of magnitude" may have to
    > > be quantified a little more thoroughly in my world.[/ref][/ref]
     

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

     

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

    So I can take your word for it?
     [/ref]
     

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

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

     

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

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

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

    addinall Guest

  16. #16

    Default Re: comparing two tables


    Captain Paralytic wrote: 
    > >
    > > That seems closer, what actually seems to do the trick is:
    > >
    > > SELECT *
    > > FROM personal_images
    > > WHERE album NOT
    > > IN (
    > >
    > > SELECT uid
    > > FROM personal_albums
    > > )[/ref]
    >
    > 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/en/rewriting-subqueries.html[/ref]

    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.

    Cleverbum Guest

  17. #17

    Default Re: comparing two tables

    On 16 Jan 2007 03:44:23 -0800, Cleverbum wrote: 

    But what are you trying to actually accomplish? Usually when someone is
    asking about rows linked to rows deleted from another table, they really
    want those rows identified so they can delete them, which means they
    want table constraints.

    --
    51. If one of my dungeon guards begins expressing concern over the conditions
    in the beautiful princess' cell, I will immediately transfer him to a
    less people-oriented position.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

Similar Threads

  1. comparing 2 tables 1 database
    By adult.swim in forum Coldfusion Database Access
    Replies: 6
    Last Post: April 28th, 04:47 PM
  2. Comparing two files
    By BradS in forum Linux / Unix Administration
    Replies: 2
    Last Post: February 1st, 04:57 PM
  3. Comparing two tables
    By mkarja in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 11th, 12:42 PM
  4. Comparing rows in 2 tables
    By DA Morgan in forum Oracle Server
    Replies: 4
    Last Post: December 18th, 06:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139