In SQL Server when I am attempting to put data into a variable but have a ISNULL around the second left outer join value in the where statement instead of providing the expected result it only returns the last "row" of data. In the example below you can see in much more detail what I mean.
The first output will show you the generic output, 6 rows of data. The second output will show you that I am only getting the last row of data in @VAR. The third output will show you that if I remove ISNULL from FRUIT_COLOR_yellow.NAME in the Order by statement I get all 6 of rows concatenated together (which is what I would think I would also get for the second output).
I want to understand why the second output is returning what it is. What am I missing?
Really I just want to know why @VAR is different than @VAR2. The real life example for this is that I was building an HTML table (which is why I was concatenating in this method) and I wanted to order by something more like COALESCE(FRUIT_COLOR_ORANGE.name, FRUIT_COLOR_YELLOW.NAME, ''). That didn't return the number of rows that I was expecting thus I tried to trace the issue and its something about when there is a ISNULL in the order by statement for the second Left Outer Join.
IF OBJECT_ID('FRUIT') IS NOT NULL
DROP TABLE FRUIT;
GO
CREATE TABLE FRUIT (
name VARCHAR(25),
color INT
);
GO
IF OBJECT_ID('FRUIT_COLOR') IS NOT NULL
DROP TABLE FRUIT_COLOR;
GO
CREATE TABLE FRUIT_COLOR
(
id INT,
name VARCHAR(25)
);
GO
INSERT into FRUIT_COLOR VALUES (1,'orange');
INSERT into FRUIT_COLOR VALUES(2,'yellow');
INSERT into FRUIT_COLOR VALUES (3,'red');
INSERT into FRUIT_COLOR VALUES (4,'blue');
GO
INSERT into FRUIT VALUES ('banana',2);
INSERT into FRUIT VALUES ('mango',2);
INSERT into FRUIT VALUES ('orange',1);
INSERT into FRUIT VALUES ('apple',3);
INSERT into FRUIT VALUES ('grapes',null);
INSERT into FRUIT VALUES ('avocado',null);
GO
--Test Control - 6 rows of data
SELECT ISNULL(FRUIT.NAME,'') + ' ' + ISNULL(FRUIT_COLOR_ORANGE.NAME, 'NOT ORANGE ') + ISNULL(FRUIT_COLOR_yellow.NAME, 'NOT YELLOW ')
FROM FRUIT
LEFT OUTER JOIN FRUIT_COLOR FRUIT_COLOR_ORANGE ON FRUIT.COLOR = FRUIT_COLOR_ORANGE.ID
AND FRUIT_COLOR_ORANGE.NAME = 'Orange'
LEFT OUTER JOIN FRUIT_COLOR FRUIT_COLOR_yellow ON FRUIT.COLOR = FRUIT_COLOR_yellow.ID
AND FRUIT_COLOR_yellow.NAME = 'yellow'
ORDER BY FRUIT_COLOR_ORANGE.name
,ISNULL(FRUIT_COLOR_yellow.NAME, 'test')
DECLARE @VAR varchar(MAX)
SET @VAR = ''
--When I attempt to put this to a variable it only shows the last row of data
SELECT @VAR = ISNULL(@VAR, '') + ISNULL(FRUIT.NAME,'') + ' ' + ISNULL(FRUIT_COLOR_ORANGE.NAME, 'NOT ORANGE ') + ISNULL(FRUIT_COLOR_yellow.NAME, 'NOT YELLOW ')
FROM FRUIT
LEFT OUTER JOIN FRUIT_COLOR FRUIT_COLOR_ORANGE ON FRUIT.COLOR = FRUIT_COLOR_ORANGE.ID
AND FRUIT_COLOR_ORANGE.NAME = 'Orange'
LEFT OUTER JOIN FRUIT_COLOR FRUIT_COLOR_yellow ON FRUIT.COLOR = FRUIT_COLOR_yellow.ID
AND FRUIT_COLOR_yellow.NAME = 'yellow'
ORDER BY FRUIT_COLOR_ORANGE.name
,ISNULL(FRUIT_COLOR_yellow.NAME, 'test')
SELECT @VAR
DECLARE @VAR2 varchar(MAX)
SET @VAR2 = ''
--If I remove the ISNULL from FRUIT_COLOR_yellow.NAME in the order by clause I get all 6 results concatonated as I would expect
SELECT @VAR2 = ISNULL(@VAR2, '') + ISNULL(FRUIT.NAME,'') + ' ' + ISNULL(FRUIT_COLOR_ORANGE.NAME, 'NOT ORANGE ') + ISNULL(FRUIT_COLOR_yellow.NAME, 'NOT YELLOW ')
FROM FRUIT
LEFT OUTER JOIN FRUIT_COLOR FRUIT_COLOR_ORANGE ON FRUIT.COLOR = FRUIT_COLOR_ORANGE.ID
AND FRUIT_COLOR_ORANGE.NAME = 'Orange'
LEFT OUTER JOIN FRUIT_COLOR FRUIT_COLOR_yellow ON FRUIT.COLOR = FRUIT_COLOR_yellow.ID
AND FRUIT_COLOR_yellow.NAME = 'yellow'
ORDER BY FRUIT_COLOR_ORANGE.name
,FRUIT_COLOR_yellow.NAME
SELECT @VAR2
