Most databases support != (popular programming languages) and <> (ANSI).
Databases that support both != and <>:
Databases that support the ANSI standard operator, exclusively:
- IBM DB2 UDB 9.5:
<>
- Microsoft Access 2010:
<>
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?
See the article in my blog for detailed performance comparison:
CROSS APPLY works better on things that have no simple JOIN condition.
This one selects 3 last records from t2 for each record from t1:
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY
(
SELECT TOP 3 *
FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
It cannot be easily formulated with an INNER JOIN condition.
You could probably do something like that using CTE's and window function:
WITH t2o AS
(
SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
FROM t2
)
SELECT t1.*, t2o.*
FROM t1
INNER JOIN
t2o
ON t2o.t1_id = t1.id
AND t2o.rn <= 3
, but this is less readable and probably less efficient.
Update:
Just checked.
master is a table of about 20,000,000 records with a PRIMARY KEY on id.
This query:
WITH q AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM master
),
t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
JOIN q
ON q.rn <= t.id
runs for almost 30 seconds, while this one:
WITH t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
CROSS APPLY
(
SELECT TOP (t.id) m.*
FROM master m
ORDER BY
id
) q
is instant.
Best Answer
To alias the table you'd have to say:
I fail to see the point of aliasing for this specific
DELETEstatement, especially since (at least IIRC) this no longer conforms to strict ANSI. But yes, as comments suggest, it may be necessary for other query forms (eg correlation).