Joining a subselect to a table in sql

Question

Is it possible to join the results of a SELECT with another table.

Like this: SELECT * FROM table1 LEFT OUTER JOIN (SELECT * FROM table 2)

I know I need to link the column but am not sure how. Is this possible?

Answer 1

You need to know what columns you are joining on. Assuming they are called ID in both tables, something like this would work:

SELECT *
FROM table1 t1
LEFT OUTER JOIN (SELECT * FROM table 2) t2 on t1.ID = t2.ID

Note that rather than using *, you should name the columns you need explicitly. This will give a more efficient query if you do not need all of the data, and will also prevent duplicate column names from being returned.

Answer 2

You can do this. The code would be something like:

(SELECT id as leftid, [other fields] FROM table1) LEFT OUTER JOIN (SELECT id rightid, [other fields] FROM table2) ON (leftid=rightid)

I didn't test it, though...