SQL query statement join and union

Question

I write a SQL statement but I can't take desired answer

Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT FIBER.ID , LIST.STATION , LIST.CODE , FIBER.NUMBER , FIBER.OWNER , " _
+ " FIBER.KIND , FIBER.CORE_NUMBER , FIBER.LENGTH , FIBER.LOSS ,FIBER.COLOR , FIBER.CABINET , FIBER.R_NUMBER , NULL AS R_STATION , NULL AS R_CODE, FIBER.COMMENT FROM LIST " _
+ " INNER JOIN FIBER " _
 + " ON FIBER.NUMBER = LIST.NUM_CODE " _
 + " UNION  ALL" _
 + " SELECT FIBER.ID , NULL AS STATION , NULL AS CODE , FIBER.NUMBER , FIBER.OWNER , " _
+ " FIBER.KIND , FIBER.CORE_NUMBER , FIBER.LENGTH , FIBER.LOSS ,FIBER.COLOR , FIBER.CABINET , FIBER.R_NUMBER , LIST.STATION AS R_STATION , LIST.CODE AS R_CODE , FIBER.COMMENT FROM LIST " _
 + " INNER JOIN FIBER " _
+ " ON FIBER.R_NUMBER = LIST.NUM_CODE " _
, objConnection)

and the answer is

![my answer must be three records but I get back six records and R_CODE , R_STATION are copied to in three record below][10]

thanks

Answer 1

Instead of using "UNION ALL" , use "UNION".

Because UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not