Select all of the files which are not parents to any other file SQL

Question

CREATE TABLE Files (
    Id INT PRIMARY KEY,
    [Name] NVARCHAR(100) NOT NULL,
    Size DECIMAL(18, 2) NOT NULL,
    ParentId INT NULL FOREIGN KEY REFERENCES Files(Id)
)

SET IDENTITY_INSERT [dbo].[Files] ON 
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (1, N'Trade.idk', CAST(2598.00 AS Decimal(18, 2)), 1, 1)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (2, N'menu.net', CAST(9238.31 AS Decimal(18, 2)), 2, 2)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (3, N'Administrate.soshy', CAST(1246.93 AS Decimal(18, 2)), 3, 3)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (4, N'Controller.php', CAST(7353.15 AS Decimal(18, 2)), 4, 4)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (5, N'Find.java', CAST(9957.86 AS Decimal(18, 2)), 5, 5)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (6, N'Controller.json', CAST(14034.87 AS Decimal(18, 2)), 3, 6)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (7, N'Operate.xix', CAST(7662.92 AS Decimal(18, 2)), 7, 7)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (8, N'file.sick', CAST(10548.35 AS Decimal(18, 2)), 8, 8)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (9, N'config.dd', CAST(8745.77 AS Decimal(18, 2)), 9, 9)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (10, N'Index.java', CAST(6121.35 AS Decimal(18, 2)), 10, 10)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (11, N'compile.ivory', CAST(1185.04 AS Decimal(18, 2)), 11, 1)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (12, N'Model.MD', CAST(4753.67 AS Decimal(18, 2)), 3, 12)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (13, N'Beat.html', CAST(907.30 AS Decimal(18, 2)), 13, 13)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (14, N'READ.img', CAST(2627.60 AS Decimal(18, 2)), 14, 7)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (15, N'Search.py', CAST(8831.43 AS Decimal(18, 2)), 15, 15)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (16, N'Controller.intro', CAST(27302.85 AS Decimal(18, 2)), 11, 1)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (17, N'Login.html', CAST(2863.23 AS Decimal(18, 2)), 16, 17)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (18, N'Administrate.go', CAST(24612.57 AS Decimal(18, 2)), 9, 18)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (19, N'READ.html', CAST(2396.47 AS Decimal(18, 2)), 8, 1)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (20, N'index.net', CAST(9261.71 AS Decimal(18, 2)), 20, 20)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (21, N'Index.class', CAST(4001.15 AS Decimal(18, 2)), 21, 21)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (22, N'config.json', CAST(6049.09 AS Decimal(18, 2)), 22, 22)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (23, N'pipeline.dd', CAST(18407.72 AS Decimal(18, 2)), NULL, 19)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (24, N'Accelerate.dd', CAST(23042.88 AS Decimal(18, 2)), 24, 19)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (25, N'Database.dd', CAST(14905.56 AS Decimal(18, 2)), NULL, 25)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (26, N'Login.db', CAST(8015.83 AS Decimal(18, 2)), NULL, 21)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (27, N'Beat.bat', CAST(21431.98 AS Decimal(18, 2)), 25, 12)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (28, N'Jason.txt', CAST(10317.54 AS Decimal(18, 2)), NULL, 28)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (29, N'Jason.exe', CAST(28209.18 AS Decimal(18, 2)), 8, 25)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (30, N'Accelerate.idk', CAST(5520.30 AS Decimal(18, 2)), 30, 1)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (31, N'file.txt', CAST(5514.02 AS Decimal(18, 2)), 27, 1)
GO
INSERT [dbo].[Files] ([Id], [Name], [Size], [ParentId], [CommitId]) VALUES (32, N'Music.jpg', CAST(917.75 AS Decimal(18, 2)), 1, 3)
GO
SET IDENTITY_INSERT [dbo].[Files] OFF
GO

I am trying to select all of the files which are NOT a parent to any other file. This was my try:

SELECT f.Id, f.[Name], CONCAT(f.Size, 'KB') AS Size
FROM Files f
WHERE f.Id  NOT IN 
    (SELECT f1.ParentId 
    FROM Files f1 
    WHERE f1.ParentId IS NOT NULL)
ORDER BY f.Id, f.[Name], Size DESC

Now I am trying to use joins to solve the same task.

SELECT *
FROM Files f
JOIN Files f1 ON f.ParentId = f1.Id

I am confused. When we use INNER JOIN, we get only the records where f.ParentId is not null. Can you help me? Some explanation will also be appreciated. Thank you in advance!

@PrebenHuybrechts, I just looked at EXCEPT. Can you give me a hint how to use it here? — Katherine, Jul 15 '20 at 15:08
Does this answer your question? [How to select all records from one table that do not exist in another table?](https://stackoverflow.com/questions/2686254/how-to-select-all-records-from-one-table-that-do-not-exist-in-another-table) — philipxy, Jun 15 '22 at 12:18

score 1 · Accepted Answer · edited Jul 15 '20 at 15:13

1

You could try with NOT EXISTS This returns all rows that don't match the specified set in the not exists expression.

SELECT *
FROM Files f1
WHERE NOT EXISTS (
  SELECT 1
  FROM Files f2
  WHERE f2.ParentId = f1.Id
)

SQLFiddle

edited Jul 15 '20 at 15:13

Suraj Kumar

5,547
8
20
42

answered Jul 15 '20 at 15:03

Preben Huybrechts

5,853
2
27
63

score -1 · Answer 2 · edited Jun 14 '22 at 10:43

-1

SELECT *
FROM Files AS f1
FULL OUTER JOIN Files as f2
ON f1.ParentId = f2.Id
WHERE f1.Id IS NULL

edited Jun 14 '22 at 10:43

Suraj Rao

29,388
11
94
103

answered Jun 14 '22 at 10:36

Ivan Stalev

1
1

1

Why full outer join? – Gert Arnold Jun 14 '22 at 18:26

Select all of the files which are not parents to any other file SQL

2 Answers2