清单 1
SELECT DISTINCT(band)
FROM BandEJB AS band,
IN (band.recordings) records
WHERE records.numberSold >
(SELECT AVG(subquery_records.numberSold)
FROM BandEJB AS subquery_band,
IN (subquery_band.recordings) subquery_records
WHERE subquery_records.recordingDate > '31/DEC/1960')
AND records.recordingDate > '31/DEC/1960'
清单 2
SELECT OBJECT(record)
FROM RecordingEJB AS record
WHERE 3 >
(SELECT COUNT(DISTINCT subquery_record.title)
FROM RecordingEJB AS subquery_record
WHERE subquery_record.numberSold > record.numberSold)
ORDERBY record.numberSold DESC
清单 3
SELECT DISTINCT OBJECT(band)
FROM BandEJB AS band,
IN (band.recordings) records
WHERE records.numberSold >
( SELECT subquery_records.numberSold
FROM BandEJB AS subquery_band,
IN (subquery_band.recordings) subquery_records
WHERE subquery_records.recordingDate < '01/JAN/1961')
如果用Oracle作为SQL引擎,你将收到这样的运行时SQL异常报告:"ORA01427: single-row subquery returns more than one row"。类似地,如果SQL引擎是SQL Server,则返回的运行时SQL异常报告为:"Server:Msg 512,Level 16,state 1,Line 1 Subquery returned more than 1 vauel"。当子查询跟在=、!=、<、<=、>、>=之后或者子查询作为一个表达式的时候,这些异常都是不允许的。
清单 4
SELECT DISTINCT OBJECT(band)
FROM BandEJB AS band,
IN (band.recordings) records
WHERE records.numberSold > ANY
( SELECT subquery_records.numberSold
FROM BandEJB AS subquery_band,
IN (subquery_band.recordings) subquery_records
WHERE subquery_records.recordingDate < '01/JAN/1961')
清单 5
SELECT OBJECT(targetBand)
FROM BandEJB AS targetBand,
BandEJB AS founderBand,
ArtistEJB AS founderArtist
WHERE targetBand.name = founderBand.founder
AND founderArtist.name = founderBand.founder
AND founderArtist.id NOT IN
(SELECT subquery_artist.id
FROM BandEJB AS subquery_band,
IN (subquery_band.artists)subquery_artist
WHERE subquery_band.name = targetBand.name
AND subquery_band.founder = targetBand.founder )
[NOT] IN和[NOT] EXISTS操作符之间的不同之处在于:[NOT] IN 用于检查其左操作数是否落在作为其右操作数的子查询所返回的集合中,而[NOT] EXISTS操作符只有一个右操作数,该操作符要检查的是作为其右操作数的子查询所返回的集合是否为空。为说明[NOT] EXISTS的用法,清单6展示了将前面例子中的[NOT] IN John Doe改写为使用[NOT] EXISTS操作符的情况。
清单 6
SELECT OBJECT(targetBand)
FROM BandEJB AS targetBand,
BandEJB AS founderBand
WHERE targetBand.name = founderBand.founder
AND NOT EXISTS
(SELECT subquery_artist.name
FROM BandEJB AS subquery_band,
IN (subquery_band.artists)subquery_artist
WHERE subquery_artist.name = targetBand.name
AND subquery_band.name = targetBand.name
AND subquery_band.founder = targetBand.founder )
标准EJB QL和隐式子查询
再考察一下清单5中讲到的[NOT] IN John Doe查询。你可能已经注意到,这种查询的表述还可以用标准EJB QL的一个查询表达出来,不过要用到NOT MEMBER OF操作符。清单7展示了了一个使用NOT MEMBER OF来实现的等价的查询。
清单 7
SELECT OBJECT(targetBand)
FROM BandEJB AS targetBand,
IN (targetBand.artists)target_artists,
BandEJB AS founderBand,
ArtistEJB AS founder_artist
WHERE targetBand.name = founderBand.founder
AND founder_artist.name = founderBand.founder
AND founder_artist NOT MEMBER OF target_artists>
EJB编译器检查NOT MEMBER OF查询产生的SQL语句所得出的结论十分有趣,编译器产生的完整的SQL语句如清单8所示:
清单 8
SELECT WL0.founder, WL0.name, WL0.startDate
FROM bands WL0, bands WL1, Artists WL2
WHERE WL0.name = WL1.founder
AND WL2.name = WL1.founder
AND WL2.id NOT IN
(SELECT WL5.id
FROM bands WL3, band_artist WL4, Artists WL5
WHERE WL3.name = WL4.band_name
AND WL3.founder = WL4.band_founder
AND WL4.artist_id = WL5.id
AND WL0.founder = WL3.founder
AND WL0.name = WL3.name)
特别有趣的地方在于:在相关子查询中,EJB QL中的NOT MEMBER OF操作符被EJB编译器转换成使用SQL语句中的[NOT] IN操作符的数据库查询。EJB QL语言中的NOT MEMBER OF操作符实际上是一个使用了相关子查询的隐藏[NOT] IN操作符。下面考虑含有显式相关子查询的清单5中所讲到的EJB QL语言中的[NOT] IN John Doe查询。为此查询产生的SQL代码如清单9所示。比较清单8中为EJB QL语言中的 NOT MEMBER OF查询产生的SQL代码和清单9中为EJB QL [NOT] IN查询生成的SQL代码,就可以发现两者实际上是一样的。因此,对于底层的DBMS(数据库管理系统)来说,这两种查询没什么区别。显然,在EJB QL中,使用NOT MEMBER OF操作符来表达这种查询要来得更简单些。
清单 9
SELECT WL0.founder,
WL0.name,
WL0.startDate
FROM bands WL0,
bands WL1,
Artists WL2
WHERE WL0.name = WL1.founder
AND WL2.name = WL1.founder
AND WL2.id NOT IN
(SELECT WL5.id
FROM bands WL3, Artists WL5, band_artist WL4
WHERE WL3.name = WL0.name
AND WL3.founder = WL0.founder
AND WL3.name = WL4.band_name
AND WL3.founder = WL4.band_founder
AND WL4.artist_id = WL5.id )
有时候,使用显式子查询可能比较有用,即使一开始看起来这没什么必要。例如:在MS SQLServer7中,给定一些测试数据,对比为EJB QL语言中的NOT MEMBER OF查询(在清单9中被转换成[NOT] IN)生成的SQL代码与为清单6中用显示子查询及[NOT] EXISTS重写的等价的EJB QL查询生成的SQL代码各自的效率,SQL Server的查询分析器会分析出有趣的结果。SQL Server将通过执行以下主要操作来评价这两种查询: