Select the records from the main table according to the 4th detail table

I have 4 tables: Table1, Table2, Table3, Table4 each one is a detail the prior one for example table4 is a detail for table3 and so on. here is my schema:

Table1:

PK1 Integer,
Name1 varchar(15),...

Table2:

PK2 Integer,
PK1 Integer, -- This is foreign key to Table1.PK1
Name2 varchar(15),...

Table3:

PK3 Integer,
PK2 Integer, -- This is foreign key to Table2.PK2
Name3 varchar(15),...

Table4:

PK4 Integer,
PK3 Integer, -- This is foreign key to Table3.PK3
Name4 varchar(15),...

I need now to select records from Table1 based on certain value from Table4 for example when Table4.PK4 = 3.

So I tried like that:

Select * from table1 where PK1 in (
  select PK1 from Table2 where PK2 in (
  select PK2 from Table3 where PK3 in (
  select PK3 from Table4 where PK4 = 3
)))

I got correct results but is it the best/optimized way or there is better SQL I should use ?

Why don’t you use all joins instead:

Select DISTINCT table1.*
from table1
    INNER JOIN table2 ON table1.PK1 = table2.PK1
    INNER JOIN table3 ON table2.PK2 = table3.PK2
    INNER JOIN table4 ON table3.PK3 = table4.PK3
WHERE table4.PK4 = 3

I think query engine should be smart enough to optimize your query, but at least from readability point of view the one I provided above is much more readable

Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Select the records from the main table according to the 4th detail table

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录