SET ANSI_NULLS ON;
CREATE TABLE #temp ( ID INT );
INSERT INTO #temp
VALUES ( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 ),
( 2 );
--go
-- Query 1
SELECT *
FROM #temp
WHERE ID >= SOME ( SELECT ID
FROM #temp );
-- Query 2
SELECT *
FROM #temp
WHERE ID >= ALL ( SELECT ID
FROM #temp );
Which of the answers below are correct?
| Choose your answer: |
|
Answers is below
Correct answer: | B，E，I Query 1 Returns 6 Rows, Query 2 Returns 1 Row, Query 1 would return the same amount of rows if a NULL row is inserted. |
Explanation: | SOME or ANY returns TRUE when the comparison specified is TRUE for any pair (scalar_expression,x) where x is a value in the single-column set; otherwise, returns FALSE. https://msdn.microsoft.com/query/dev14.query?appId=Dev14IDEF1&l=EN-US&k=k(some_TSQL);k(sql13.swb.tsqlresults.f1);k(sql13.swb.tsqlquery.f1);k(MiscellaneousFilesProject);k(DevLang-TSQL)&rd=true Each row's ID is tested to see if it is greater or equal to any ID in the table, including itself. Since each ID is equal to itself, and possibly greater than others, all rows are displayed pass the test. Passing the some/any test only means that the row being tested is displayed. ----- ALL returns TRUE when the comparison specified is TRUE for all pairs (scalar_expression,x), when x is a value in the single-column set; otherwise returns FALSE. https://msdn.microsoft.com/en-us/library/ms178543.aspx Each rows's ID is tested to see if it is greater or equal to ALL the IDs from the table. Since only ID 5 is greater than or equal to all IDs including itself, it is the only row displayed. ------ When a NULL is inserted as a seventh row, Query 2 still returns 6 rows. The NULL row is not returned because a NULL ID will fail the >= comparisons against any value. Query 1 will return zero rows because all ID values will fail the >= comparison against the NULL row. This is currently true whether ANSI_NULLS are set ON or OFF. Oddly, if Query 1's comparision is changed from (>=) to (=) it will return all seven rows when ANSI_NULLS are off. |
