1go
2
3DROP TABLE IF EXISTS #tabStudent;
4DROP TABLE IF EXISTS #tabClass;
5
6go
7
8CREATE TABLE #tabClass
9(
10   ClassGuid   uniqueIdentifier  not null  default newid(),
11   ClassName   nvarchar(32)      not null
12);
13
14CREATE TABLE #tabStudent
15(
16   StudentGuid   uniqueIdentifier  not null  default newid(),
17   StudentName   nvarchar(32)      not null,
18   ClassGuid     uniqueIdentifier      null   -- Foreign key.
19);
20
21go
22
23INSERT INTO #tabClass
24      (ClassGuid, ClassName)
25   VALUES
26      ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
27      ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
28      ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting')
29;
30
31INSERT INTO #tabStudent
32      (StudentName, ClassGuid)
33   VALUES
34      ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
35      ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
36      ('Betty Boot' , 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
37      ('Betty Boot' , '98509D36-A2C8-4A65-A310-E744F5621C83'),
38      ('Carla Cap'  , null)
39;
40
41go
42
43SELECT
44      c.ClassName,
45      s.StudentName
46   from
47                       #tabClass   as c
48      RIGHT OUTER JOIN #tabStudent as s ON s.ClassGuid = c.ClassGuid
49   --where
50   --   c.ClassName LIKE '%Math%'
51   order by
52      c.ClassName,
53      s.StudentName
54   FOR
55      JSON AUTO
56      --, INCLUDE_NULL_VALUES
57;
58
59go
60
61DROP TABLE IF EXISTS #tabStudent;
62DROP TABLE IF EXISTS #tabClass;
63
64go
65

1SELECT name, surname  
2FROM emp  
3FOR JSON AUTO;
4