Query optimisation in SQL Server Management Studio

Question

In the query given below, in the where condition the table 'a' is not accessible. How can I make it accessible without affecting the logic of the code?

Thanks in advance.

select * 
from Training_Data.dbo.test a
cross apply (select * from Training_data.dbo.test_table) b
where exists (select 1 from a)

Answer 1

You can access your referenced tables columns in any EXIST inside your WHERE, you just need a correct syntax.

Example: SELECT if there's a record with same value in another table (reference to a column from table test:

select 
    * 
from 
    Training_Data.dbo.test a
    cross apply (select * from Training_data.dbo.test_table) b
where 
    exists (
        select 
            1 
        from 
            Training_data.dbo.another_test_table c 
        where 
            a.someColumn = c.anotherColumn)

Example: SELECT if there are records on a table (without referencing any column):

select 
    * 
from 
    Training_Data.dbo.test a
    cross apply (select * from Training_data.dbo.test_table) b
where 
    exists (
        select 
            1 
        from 
            Training_Data.dbo.test c)

For this last example, if you want to select rows from a table only if they exist in the same table, then your SELECT will already handle that, since it will fetch the rows that are in that table.