1

Joins are usually used to fetch data from 2 tables using a common factor from either tables

Is it possible to use a join statement using a table and results of another SQL statement and if it is what is the syntax

Improve this question

7 Answers 7

Reset to default
3

Sure, this is called a derived table

such as:

select a.column, b.column from table1 a join (select statement) b on b.column = a.column

keep in mind that it will run the select for the derived table in entirety, so it can be helpful if you only select things you need.

EDIT: I've found that I rarely need to use this technique unless I am joining on some aggregated queries.... so I would carefully consider your design here. For example, thus far most demonstrations in this thread have not required the use of a derived table.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Derived tables are great are are usually faster than correlated subqueries. YOu can also use with staements instead but frankly I find them harder to read and interpret (of course I've been reading derived tables for ten years). One thing to remember is that any derived tblae must be a given an alias or the query will not work.
I am speaking from the context of comparably large datasets (though, of course I could still be wrong!). Let's say I wanted to join an aggregate sum within a date range. The derived table is queried before it's assigned to its alias. So it should be faster to say JOIN (select sum(a), b from table where b = c group by b) d than it would be to do JOIN (select sum(a), b from table group by b) d WHERE d.b = c
1

It depends on what the other statement is, but one of the techniques you can use is common table expressions - this may not be available on your particular SQL platform.

In the case of SQL Server, if the other statement is a stored procedure, you may have to insert the results into a temporary table and join to that.

It's also possible in SQL Server (and some other platforms) to have table-valued functions which can be joined just like a view or table.

Improve this answer

Comments

1 
select * from TableA a inner join (select x from TableB) b on a.x = b.x
Improve this answer

Comments

1 
Select c.CustomerCode, c.CustomerName, sq.AccountBalance From Customers c Join ( Select CustomerCode, AccountBalance From Balances )sq on c.CustomerCode = sq.CustomerCode
Improve this answer

Comments

1

Sure, as an example:

SELECT * FROM Employees E INNER JOIN ( SELECT EmployeeID, COUNT(EmployeeID) as ComplaintCount FROM Complaints GROUP BY EmployeeID ) C ON E.EmployeeID = C.EmployeeID WHERE C.ComplaintCount > 3
Improve this answer

Comments

0

It is. But what specifically are you looking to do?

That can be done with either a sub-select, a view or a temp table... More information would help us answer this question better, including which SQL software, and an example of what you'd like to do.

Improve this answer

1 Comment

You shouldn't use a subselect (if it is correlated which most of them are) over a derived tble, that is asking for performance problems as subselects operate row by row and derived tables operate on a set. Views offer no performance improvements over derived tables.
0

Try this:

SELECT T1.col1, t2.col2 FROM Table1 t1 INNER JOIN (SELECT col1, col2, col3 FROM Table 2) t2 ON t1.col1 = t2.col1
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.