Intersect in MySQL

cy163 · #1 (**permalink**) 11-08-08, 10:23

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

r937 · #2 (**permalink**) 11-08-08, 14:30

it looks fine, assuming that the WHERE clauses are different

what are they, by the way?

cy163 · #3 (**permalink**) 11-08-08, 21:20

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

r937 · #4 (**permalink**) 11-09-08, 00:07

use an INNER JOIN

what happened to the first example?

cy163 · #5 (**permalink**) 11-10-08, 00:02

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?

r937 · #6 (**permalink**) 11-10-08, 00:04

no, INNER JOIN was for your second problem, the one with the two tables that had only a single column in each of them

cy163 · #7 (**permalink**) 11-10-08, 00:20

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?

r937 · #8 (**permalink**) 11-10-08, 06:15

it worked well because it works

INNER JOIN is simpler and more efficient

cy163 · #9 (**permalink**) 11-10-08, 10:18

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

r937 · #10 (**permalink**) 11-10-08, 10:30

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

Internet Services

Web Development

Digital Marketing

Consumer Tech