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
+3
A:
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.
Matthew PK
2010-10-01 16:58:31
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.
HLGEM
2010-10-01 21:40:41
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
Matthew PK
2010-10-04 16:59:12
+1
A:
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.
Cade Roux
2010-10-01 16:58:52
+1
A:
select *
from TableA a
inner join (select x from TableB) b
on a.x = b.x
Joe Stefanelli
2010-10-01 16:58:57
A:
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.
grmartin
2010-10-01 16:59:16
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.
HLGEM
2010-10-01 21:42:42
+1
A:
Select c.CustomerCode, c.CustomerName, sq.AccountBalance
From Customers c
Join (
Select CustomerCode, AccountBalance
From Balances
)sq on c.CustomerCode = sq.CustomerCode
Barry
2010-10-01 16:59:31
+1
A:
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
LittleBobbyTables
2010-10-01 17:00:15
A:
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
Josh
2010-10-01 17:00:54