Mysql query results intersection method

This article will introduce in detail how to implement the intersection of the result sets returned by SQL query in MySQL. Friends in need can refer to

1
How to implement the following SQL query in MySQL
(SELECT S.Name
FROM STUDENT S, TRANSCRIPT T
WHERE S.StudId = T.StudId AND T.CrsCode = 'CS305' )
INTERSECT
(SELECT S.Name
FROM STUDENT S, TRANSCRIPT T
WHERE S.StudId = T.StudId AND T.CrsCode = 'CS315')
Please give me your advice. Thanks in advance~
Solution:
Get the intersection
select a.* from
(
SELECT S.Name
FROM STUDENT S, TRANSCRIPT T
WHERE S.StudId = T.StudId AND T.CrsCode = 'CS305'
) as a
cross join
(
SELECT S.Name
FROM STUDENT S, TRANSCRIPT T
WHERE S.StudId = T.StudId AND T.CrsCode = 'CS315'
) as b on a.Name = b.Name;

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

3.
Intersection:
SELECT * FROM table1 AS a JOIN table2 AS b ON a.name =b.name
Example:
Table a:
FieldA
001
002
003
Table b:
FieldA
001
002
003
004
Please tell me how to get the following result set, that is, the intersection of rows of tables A and B
FieldA
001
002
003
Answer: select a.FieldA from an inner join b on a.FieldA =b.FieldA
Difference set:
NOT IN represents the difference set
SELECT * FROM table1 WHERE name NOT IN (SELECT name FROM table2)

The above is the detailed content of Mysql query results intersection method. For more information, please follow other related articles on the PHP Chinese website!