Sub-Query (SubQ now on) is query within query. We can write SubQ at many levels just followed is example of various places where SubQ is generally used.
SELECT Field1,
(SELECT count(Field2)
FROM [TableName2]
) as Field2
FROM [TableName1]
INNER JOIN (
SELECT Field1
FROM [TableName3]) T3
ON T1.Field1=T3.Field1
WHERE Field3 IN (SELECT Field3 FROM [TableName4])
In SQL server (or may be other DB system but I don’t know about them), SubQ can reference columns from Parent query. This brings some interesting mistakes. For explaining some simple but very dangerous issue, we are using following tables.
Here is the simple query, with clear bug in it.
DELETE
FROM Users
WHERE UserId IN
(SELECT UserId
FROM Portals
WHERE PortalId=36)
While executing this error, I was expecting this to be generating error but it will work fine. Worst part of this query is, it will delete ALL records of Users table.
Reason behind this is, while SQL server executes SubQ, it is not checking for invalid columns for a SubQ w/o alliasname or tablename prefix (on columns). It executes properly, if it finds given column in any of tables specified in whole query.
Now execute below query
DELETE
FROM Users
WHERE Users.UserId IN
(SELECT Portals.UserId
FROM Portals
WHERE Portals.PortalId=36)
This will give error “Invalid column name 'UserId'”. Here, it is clearly visible that UserId is not exist in Portals table and SQL server is giving error for that.
Below is correct query to delete specific portal wise users
DELETE
FROM Users
WHERE Users.UserId IN
(SELECT UserPortals.UserId
FROM UserPortals
WHERE UserPortals.PortalId=36)
Conclusion:
- Don’t specify columns without Alias or tablename in column list in any type of queries otherwise you will get unexpected behavior. Always give alias to table name and specify columns like [aliasname].[columname] or [tablename].[columname ]
- Execute each Sub-Query individually , to check for invalid columns