three-tables-and-strange-behaviour-of-join.sql

-- Таблица №1:
CREATE TABLE [dbo].[tbl1](
  [id] [int] NULL,
  [_int] [int] NULL,
  [_str] [varchar](1) NULL
)

-- Таблица №2:
CREATE TABLE [dbo].[tbl2](
  [id] [int] NULL,
  [_int] [int] NULL,
  [_str] [varchar](1) NULL,
  [_decimal] [decimal](18, 0) NULL,
  [_double] [float] NULL,
  [cid] [int] NULL
)

-- Таблица №3:
CREATE TABLE [dbo].[tbl3](
  [id] [int] NULL,
  [cid] [int] NULL,
  [_int] [int] NULL
)

-- Данные таблицы №1:
INSERT INTO [dbo].[tbl1]
           ([id]
           ,[_int]
           ,[_str])
     VALUES
          (4, 4, 'a'),
          (2, 5,	NULL),
          (3, 5, NULL)  

-- Данные таблицы №2:             
INSERT INTO [dbo].[tbl2]
           ([id]
           ,[_int]
           ,[_str]
           ,[_decimal]
           ,[_double]
           ,[cid])
     VALUES
  (1,	1,	NULL,	0,	0,	1),
	(2,	2,	NULL,	0,	0,	2),
	(3,	3,	'a',	NULL,	NULL,	NULL)
             
-- Данные таблицы №3:
INSERT INTO [dbo].[tbl3]
           ([id]
           ,[cid]
           ,[_int])
     VALUES
	(1,	2,	1),
	(2,	2,	2),
	(3,	3,	3),
	(4,	3,	4)             
             
-- Запрос:
SELECT tbl1.id as id1, tbl2.id as id2, tbl3.id as id3 FROM tbl1 
RIGHT JOIN tbl2 ON tbl1.id = tbl2.cid
LEFT JOIN tbl3 ON tbl2.id = tbl3.id
group by tbl1.id, tbl2.id, tbl3.id
order by tbl1.id, tbl2.id, tbl3.id