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?

Solution 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

Solution 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

Solution 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).

Solution 4

SELECT CustomerId,
       Name,
       Address
FROM Table1 M
INNER JOIN Table2 C ON M.CustomerId=C.CustomerId
WHERE CustomerId IN
    (SELECT CustomerId
     FROM Table1 M
     INNER JOIN Table2 ON M.CustomeID=C.CustomerId)
ORDER BY CustomerId,
         Name,
         Address

View more solutions