SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Problem with JOIN query

    Hello guys!
    I need your help.

    I have tow table in MYSQL:
    1. tbl_login 5.739 rows, alias A;
    2. tbl_mailing_list 3.736 rows, alias B

    The field to be related tbl_login (A) and tbl_mailing_list (B) is e`email`.
    I need to know any emails of the table B are not present in table A and try this query:
    Code:
    SELECT
    	A.email,
    	B.email
    FROM
    	tbl_mailing_list A
    JOIN tbl_login B ON A.email = B.email
    
    2690 rows in set
    but these are the lines that have emails matching between the two tables...

    Thank you for the suggestion.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    "rows in one table not present in another" = LEFT OUTER JOIN with IS NULL test
    Code:
    SELECT B.email
      FROM tbl_login B
    LEFT OUTER
      JOIN tbl_mailing_list A
        ON A.email = B.email
     WHERE A.email IS NULL
    in order to prevent these situations from happening, you need to learn about foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you for your help.

    I tried your suggestion, but in the output I have all email present in the tbl_login and not present in tbl_mailing_list.

    I need the opposite:
    all email present in the tbl_mailing_list and not present in tbl_login.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh shoot, i did it the wrong way around, sorry

    okay, just reverse the tables in the LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok , thank you.
    This is the new output:
    Code:
    SELECT B.email
      FROM tbl_mailing_list B
    LEFT OUTER
      JOIN tbl_login A
        ON A.email = B.email
     WHERE A.email IS NULL
    
    Time: 0.031ms
    1039 rows in set
    I try this other query and I have the same output.
    What is the difference?
    What is the most performant?
    Code:
    SELECT
    	email
    FROM
    	tbl_mailing_list
    WHERE
    	email NOT IN (SELECT email FROM tbl_login);
    
    Time: 0.031ms
    1039 rows in set

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    What is the difference?
    no difference in output


    Quote Originally Posted by cms9651 View Post
    What is the most performant?
    interesting question

    sometimes NOT IN subqueries are slower

    this time it appears they performed the same
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot!
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •