For this blog post we will use the following two tables as examples, the scripts are written in SQL for MSSQL but it should be transferable to most databases:
CREATE TABLE Entities(
Id INT IDENTITY(1,1) PRIMARY KEY (ID) NOT NULL,
[Text] [nvarchar](max) NOT NULL,
)
CREATE TABLE SubEntities(
Id INT IDENTITY(1,1) PRIMARY KEY (ID) NOT NULL,
EntitiesId INT NOT NULL,
CreationTime DateTime2 NOT NULL,
CONSTRAINT FK_Entities_Id FOREIGN KEY (EntitiesId)
REFERENCES [dbo].[Entities] (Id)
)
INSERT INTO [dbo].[Entities] ([Text]) VALUES ('someText1');
INSERT INTO [dbo].[SubEntities] ([EntitiesId],[CreationTime]) VALUES (1, '2022-01-01');
INSERT INTO [dbo].[SubEntities] ([EntitiesId],[CreationTime]) VALUES (1, '2021-01-01');
In the above we create a table called Entities with a simple int as Id and a Text column. We then create a second table called SubEntities with an Id, EntitiesId column as a foreign key to Entities and a CreationTime DateTime. Some dummy data is then inserted into the tables. We can make a sub select (placing a select in the where clause) by simply adding it as part of the WHERE clause and wrapping it in ():
SELECT * FROM [dbo].[Entities] e
WHERE e.Id = 1
AND (SELECT COUNT(*)
FROM [SubEntities] se
WHERE se.EntitiesId = e.Id
) = 2
In the above we select the Entities row with Id 1, but only if it has 2 SubEntities pointing to it. This is an example of a sub queries or "having a select statement inside of your where clause". Alternative to a sub query you can often use a join instead.
You can also place a sub query as part of the data you are selecting, for example if we want the MAX CreationTime from our related SubEntities along with data of our Entities we can write the following:
SELECT *, (SELECT MAX(CreationTime)
FROM [SubEntities] se
WHERE se.EntitiesId = e.Id
) As SubEntitiesCount
FROM [dbo].[Entities] e
WHERE e.Id = 1
Getting the Max DateTime is a common use case with sub queries.
That is all
I hope you found this helpful, please leave a comment down below with your thoughts!