Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: Intersect in MySQL

    Hi all,

    THere is no intersect statement in MySQL. I figure out a workaround as shown below. However, I am not sure if it is correct or not. Please help.

    If it is correct. Then Will it perform well in terms of executing speed.
    Code:
    SELECT * FROM (
    	SELECT DISTINCT col1 FROM t1 WHERE...
    	UNION ALL
    	SELECT DISTINCT col1 FROM t1 WHERE...
    ) AS tbl
    GROUP BY tbl.col1 HAVING COUNT(*) = 2

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it looks fine, assuming that the WHERE clauses are different

    what are they, by the way?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    Quote Originally Posted by r937
    it looks fine, assuming that the WHERE clauses are different

    what are they, by the way?

    I would like to get the intersection of two tables. Both of them has onle one colume, titled 'Employee_ID', the followings are example tables of the two tables.

    tb1
    Code:
    Employee_ID
    0001
    0002
    0003
    0004
    tb1
    Code:
    Employee_ID
    0001
    0002
    I would like to get the employee_IDs appear in the both tables.
    i.e.,
    Code:
    0001
    0002

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use an INNER JOIN

    what happened to the first example?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    130
    Quote Originally Posted by r937
    use an INNER JOIN

    what happened to the first example?
    I have tested with

    Code:
    SELECT * FROM (
    	SELECT DISTINCT col1 FROM t1 WHERE...
    	UNION ALL
    	SELECT DISTINCT col1 FROM t1 WHERE...
    ) AS tbl
    GROUP BY tbl.col1 HAVING COUNT(*) = 2
    it worked well.
    DO you mean INNER JOIN can do the same thing too?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, INNER JOIN was for your second problem, the one with the two tables that had only a single column in each of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2007
    Posts
    130
    Quote Originally Posted by r937
    no, INNER JOIN was for your second problem, the one with the two tables that had only a single column in each of them

    strange, I used the UNION ALL method for the case of two tables that had only a single column in each of them. It worked well. Why?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it worked well because it works

    INNER JOIN is simpler and more efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2007
    Posts
    130
    Thanks, r937.

    Do you mean both 'INNER JOIN' and 'UNION ALL' works in the case of the two tables that had only a single column in each of them.

    On the other hand, *ONLY* 'UNION ALL' works in the cases when there are more than one columns/fields in the table?

    Could you please show me how to implement set intersection using 'INNER JOIN' for the case of the two tables that had only a single column in each of them

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cy163
    Could you please show me how to implement set intersection using 'INNER JOIN' for the case of the two tables that had only a single column in each of them
    Code:
    SELECT tb1.Employee_ID
      FROM tb1
    INNER
      JOIN tb2
        ON tb2.Employee_ID = tb1.Employee_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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