In SQL or MySQL, can we join a table and a subquery result?

Question

Can we join a table with the result of a subquery, such as:

select name from gifts
    LEFT OUTER JOIN (select giftID from gifts) ...

If not, can it be done by some methods, such as creating a temporary table?

P.S. Can a subquery only appear using IN or NOT IN, or EXISTS or NOT EXISTS?

Answer 1

yes, sql works on sets, a subquery returns a set as result, so this is possible.

you have to give the subquery a name: (select * from table) as sub

Answer 2

yes you can use a select as an INNER JOIN you just have to give it an alias:

SELECT Name FROM Transactions T
INNER JOIN (SELECT Distinct customerID As CustomerID FROM Customers) A 
ON A.CustomerID = T.CustomerID

Answer 3

Another way, could be to create a VIEW of the subquery. Then do a JOIN as you would normally would (by referencing the VIEW).