Can any one explain this SQL Query?

Question

Today, my boss told the below SQL query and went out without explaining it. Its working good. But i want to know the way how it works.

SELECT NAME 
  FROM PERMISSIONTOKENS 
 WHERE ID IN (SELECT TOKENID 
                FROM ROLETOKENASSOCIATION 
               WHERE ROLEID = '1');

Answer 1

It select field NAME from rows for which ID is in this set of values : all the TOKENID from the row of the table ROLETOKENASSOCIATION for which ROLEID = '1'.

Answer 2

There are two queries here.

The inner query:

SELECT TOKENID FROM ROLETOKENASSOCIATION WHERE ROLEID = '1'

Will get all of the TOKENIDs from ROLETOKENASSOCIATION that have a ROLEID of 1.

The outer query:

SELECT NAME FROM PERMISSIONTOKENS WHERE ID IN(...)

This will get all of the names from PERMISSIONTOKENS that have an ID that was in the result set of the inner query.

You might be able to re-write this with joins instead of using IN, if you don't like the syntax of IN.

Answer 3

SELECT TOKENID FROM ROLETOKENASSOCIATION WHERE ROLEID = '1'

It selects all TOKENID where the ROLEID is 1. This is a subquery of the query:

SELECT NAME FROM PERMISSIONTOKENS WHERE ID IN (...)

The (...) is the set of all TOKENID selected. Therefore, the whole query will select the NAMEs, where the ID is one of those TOKENID with ROLEID = 1.

Answer 4

For each TokenID in the RoleTokenAssociation table, where the RoleId is "1", it will return the name of that token from PermissionTokens table.

Answer 5

This is a subquery.

Basically the inner SELECT retrieves the token ID's, which are then fed back into the outer SELECT query. You could also achieve the same using an INNER JOIN:

    SELECT name 
      FROM PERMISSIONTOKENS pt
INNER JOIN ROLETOKENASSOCIATION rta ON rta.TOKENID = pt.ID
     WHERE rta.ROLEID = '1';