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
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!