Can we insert one SQL query inside another SQL query while using join?

Question

What I exactly want to do is like this:

1. I have two tables, Table 1 has 2 columns A (statements) and column B (userids)
2. Table 2 has columns G (username) and H (userids)

Column B and column H hold the same type of data (integer) but column names are different.

Output: I need to have both the column from table 1 i.e. column A and column B and column G from table 2 based on column H.

Basically, for every statement in table one it has corresponding userids, but usernames are there in table 2. So how can I map it. I know we need to write join here. but the concern is as below

Secondly, the trouble is table 1 is output of a select query . So can we input a select query as table 1 while writing a join?

Answer 1

These are called derived tables and are very common. The follow a pattern like this:

SELECT *
FROM TABLE1
INNER JOIN (
   SELECT *
   FROM TABLE2
) AS T2 ON TABLE1.COLUMN=T2.COLUMN

The inner join's SQL should be able to be run standalone to produce the records you expect for that "table".

The accepted answer to this question is a good example.