示例 INTERSECT 查询
将以下示例与第一个 UNION 示例进行比较。这两个示例之间的唯一差异是所使用的集合运算符,但结果完全不同。仅其中一行相同:
235494 | 23875 | 8771
这是在包含 5 行的有限结果中,同时在两个表中找到的唯一行。
select listid, sellerid, eventid from listing
intersect
select listid, sellerid, eventid from sales
order by listid desc, sellerid, eventid
limit 5;
listid | sellerid | eventid
--------+----------+---------
235494 | 23875 | 8771
235482 | 1067 | 2667
235479 | 1589 | 7303
235476 | 15550 | 793
235475 | 22306 | 7848
(5 rows)
下面的查询查找 3 月份同时在纽约和洛杉矶举办的活动(已销售这些活动的门票)。这两个查询表达式之间的差异是 VENUECITY 列上的约束。
select distinct eventname from event, sales, venue
where event.eventid=sales.eventid and event.venueid=venue.venueid
and date_part(month,starttime)=3 and venuecity='Los Angeles'
intersect
select distinct eventname from event, sales, venue
where event.eventid=sales.eventid and event.venueid=venue.venueid
and date_part(month,starttime)=3 and venuecity='New York City'
order by eventname asc;
eventname
----------------------------
A Streetcar Named Desire
Dirty Dancing
Electra
Running with Annalise
Hairspray
Mary Poppins
November
Oliver!
Return To Forever
Rhinoceros
South Pacific
The 39 Steps
The Bacchae
The Caucasian Chalk Circle
The Country Girl
Wicked
Woyzeck
(16 rows)