Inner Joins with Derived Tables

Question

I have a question about the basic syntax of joining with derived tables.

Is this teh basic syntax that is used:

select *
from table1 a

inner join (select * from table2) as T1

on 1.ID = T1.ID

Will that work?

http://stackoverflow.com/questions/26559392/difficulty-understanding-logic-of-joines#answer-26559409 — Dmitry Teplyakov, Oct 31 '14 at 19:33
I'm asking about joining to derived tables. In my previous question, I did not ask about derived tables. — Neil Keicher, Oct 31 '14 at 19:36

score 11 · Accepted Answer · answered Oct 31 '14 at 20:20

11

you're asking about joining two subqueries? try:

select * from
(select * from table1) t1 inner join
(select * from table2) t2 on
t1.id = t2.id

answered Oct 31 '14 at 20:20

Beth

9,531
1
24
43

So, if I place the derived table in the JOIN clause (as you did above), I can join it to other tables in my query? – Neil Keicher Oct 31 '14 at 20:35

score 1 · Answer 2 · answered Oct 31 '14 at 21:01

1

In your posted query change on 1.ID = T1.ID to on a.ID = T1.ID and it should work fine but why do you even need that? You can directly join with table1 and no need of inline/derived table like

select *
from table1 a
inner join 
table2 T1
on a.ID = T1.ID

answered Oct 31 '14 at 21:01

Rahul

76,197
13
71
125

Okay, thanks Rahul. You and Beth both gave pretty good answers. I'll mark hers as the answer, but only because she posted first. But thank you so much for your help. I appreciate it! – Neil Keicher Oct 31 '14 at 21:06

score 0 · Answer 3 · answered May 14 '21 at 16:02

Also useful if you're trying to use derived table defined using VALUES, i.e., to get around the 1000 row limit on inserts to temporary tables (multiple row insert). Make sure to specify the names of the variables/columns in the VALUES statement i.e., (ids), after the alias i.e., v.

Example:

SELECT *
FROM (VALUES (1,'c1'),(2,'c2'),(3,'c3')) v (ids, textcol)
JOIN TABLE1 t1 on t1.ids = v.ids

Inner Joins with Derived Tables

3 Answers3

Linked