Need to check inner join and subquery

Question

I wrote the internal equivalent of a subquery. Can someone tell me if this is the right way to do things, and if this is a more efficient way. Filters the ON keyword just like the where clause

Subquery

select 
        companyId,
        fiscalYear,
        fiscalQuarter,
        periodenddate
    into #PeriodTbl
    from(   
        select
            fp.companyId,
            fp.fiscalYear,
            fp.fiscalQuarter,
            fi.periodenddate,
            ROW_NUMBER() OVER (PARTITION BY fp.companyId, fp.fiscalYear, fp.fiscalQuarter ORDER BY fi.periodEndDate DESC) rowno
        from ciqfinperiod fp 
            join ciqfininstance fi on fi.financialperiodid = fp.financialperiodid
        where fp.periodtypeid = 4
            and fi.periodenddate > @date
            and fi.latestforfinancialperiodflag = 1
            and latestfilingforinstanceflag = 1 
            and fp.companyId in (select id from #companyId)

        ) a
    where a.rowno = 1

internal connection

select 
        companyId,
        fiscalYear,
        fiscalQuarter,
        periodenddate
    into #PeriodTbl
    from(   
        select
            fp.companyId,
            fp.fiscalYear,
            fp.fiscalQuarter,
            fi.periodenddate,
            ROW_NUMBER() OVER (PARTITION BY fp.companyId, fp.fiscalYear, fp.fiscalQuarter ORDER BY fi.periodEndDate DESC) rowno
        from ciqfinperiod fp inner join #companyId ci on fp.companyId = ci.id
            join ciqfininstance fi on fi.financialperiodid = fp.financialperiodid
        where fp.periodtypeid = 4
            and fi.periodenddate > @date
            and fi.latestforfinancialperiodflag = 1
            and latestfilingforinstanceflag = 1 
            --and fp.companyId in (select id from #companyId)

        ) a
    where a.rowno = 1

Answer 1

In the general case INNER JOIN, INthe subquery is not exactly the same. The problem is that it INNER JOINcan enter duplicate lines.

In your case, this seems unlikely because you are choosing identifiers.

, #companyId , ( ). , .

, INNER JOIN .