We Care Through What We Share
  • 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:

    1. 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 ]
    2. Execute each Sub-Query individually , to check for invalid columns

     

     

Words From Our Clients

0Years In Operation
0Loyal Clients
0Successful Projects

Tell Us About Your Project