EXCEPT/INTERSECT, CASE/ISNULL/COALESCE in SQL SERVER

EXCEPT/INTERSECT, CASE/ISNULL/COALESCE in SQL SERVER
EXCEPT and INTERSECT

General explanation: Compare the results of two queries and return non-repeated values.

EXCEPTFrom the left query, all non-repeated values not found in the right query are returned.

INTERSECTReturns all non-repeated values in both the left and right queries.

The following are the basic rules that combine the result sets of two queries that use the distinct T or INTERSECT:

• The columns and columns in all queries must be in the same order.

• The data type must be compatible.

Assume that the testid in the test table contains, and 4. The id in the test2 table contains, and.

select testid from testintersectselect id from test2

The result is only 1, 2, 3, 4.

select id from test2exceptselect testid from test

The result is only 5, 6.

Compare CASE, ISNULL, COALESCE

ISNULL and COALESCE are basically the same, which is equivalent to the CASE statement:

CASE

WHEN (expression1 is not null) THEN expression1

WHEN (expression2 is not null) THEN expression2

...

ELSE expressionN

END

However ISNULLAnd COALESCEOr Different.

Select isnull (NULL, NULL) returns NULL.

Select coalesce (NULL, NULL): at least one parameter cannot be blank.

ISNULLOnlyTwo Parameters, Such as ISNULL (1, NULL), andCOALESCEYou can haveMultiple Parameters, Such as COALESCE (NULL, 3, NULL, 1 ).

ISNULLThe final return value type is the same as the first parameter type. If the two parameters have different types and the final return value is equal to the second parameter, the second parameter is implicitly converted to the type of the first parameter and then returned.

For example,

DECLARE @ Field1 char (4), @ Field2 char (50) SET @ Field2 = 'leewhoee ee 'select isnull (@ Field1, @ Field2) -- Return 'left' select coalesce (@ Field1, @ Field2) -- Return 'leewhoee University'