G
G
GeorgeRu2020-02-27 12:02:11
SQL
GeorgeRu, 2020-02-27 12:02:11

Where is it better to put the condition “t2.[p] = @p“, in JOIN or in WHERE?

Table t2 is large, the condition restricts the sample.

SELECT *
     FROM [dbo].[table1] AS t1
FULL JOIN [dbo].[table2] AS t2 ON t2.[id] = t1.[t2_id]
                              AND t2.[p] = @p
    WHERE t2.[p] = @p
      AND (t1.[id] IS NULL
        OR t2.[id] IS NULL)

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Mikhail E, 2020-02-27
@Mikhail_E

perhaps in a subquery to t2?

G
GeorgeRu, 2020-02-27
@GeorgeRu

FULL JOIN implies that all records from both tables are needed,
the WHERE condition will limit the final selection, so it must be set in JOIN

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question