CODEWITHSUNDEEP
sqlmysqldatabase
user329820
user329820

Reputation: 985

explain these select statements!

I can not get the difference betwwn these statements? would you please help me,I have read some sample of select statements but I did not get these ones.

SELECT 'B' FROM T WHERE A = (SELECT NULL);
SELECT 'C' FROM T WHERE A = ANY (SELECT NULL);    
SELECT 'D' FROM T WHERE A = A;

I use MySQL

EDITED: also it has a conditional part that : SET ANSI_NULLS is OFF

Upvotes: 1

Views: 164

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53830

ANSI_NULL ON

SELECT 'B' FROM T WHERE A = (SELECT NULL);

is the same as:

SELECT 'B' FROM T WHERE A = NULL;

Which always returns an empty set because anything compared to NULL returns NULL, not TRUE. You might try this:

SELECT 'B' FROM T WHERE A IS NULL;

The second query is basically the same and will return an empty set:

SELECT 'C' FROM T WHERE A = ANY (SELECT NULL);

If A has a value, the following will return all rows:

SELECT 'D' FROM T WHERE A = A;

Except, if A IS NULL, it will return an empty set.

ANSI_NULL OFF

SELECT 'B' FROM T WHERE A = (SELECT NULL);

is the same as:

SELECT 'B' FROM T WHERE A = NULL;

Which returns all rows where A IS NULL.

The second query is basically the same and will return rows where A IS NULL:

SELECT 'C' FROM T WHERE A = ANY (SELECT NULL);

The following will always return all rows, regardless if A IS NULL:

SELECT 'D' FROM T WHERE A = A;

Upvotes: 3

Related Questions