Have Geek Brain

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

Is it possible to join a table with the result of a subquery like:

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

If not, can this be done with some methods like creating a temporary table?

PS Can a subquery only appear using IN or NOT IN or EXISTS or NOT EXISTS?

+2


a source to share


4 answers


yes, sql is working on sets, the subquery returns the result as a result, so it's possible.



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

+6


a source


yes you can use select as an INNER JOIN, you just need 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
+5


a source


Another way would be to create a VIEW subquery. Then do the UNION as usual (referring to VIEW).

+1


a source 


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
0


a source






More articles:


All Articles