Old IN vs. Exists vs. Left Join (where ___ Is Is Is Null); Representation

Question

I found myself in a pretty pickle. I have tables from only one column (lists of restrictions or inclusion) that are more or less varchar (25), but the fact is that I will not have time to index them before using them in the main query and, depending on how important this is, I don’t know how many rows in each table. The base table at the heart of all this is about 1.4 million rows and about 50 columns.

My assumptions are as follows:

IN is not recommended in cases where many values ​​(strings) are returned, because it looks, although the values ​​are sequential, right? (IN in the subquery did not pass the value directly)

Combining (INNER to enable and LEFT and checking for Nulls when suppressing) are best for large data sets (more than 1k rows or so for mach to)

EXISTS has always interested me because it seems to perform a subquery for each row (all 1.4 million? Yikes.)

In my hint, if possible, get the suppression table score and use either IN (for rows under 1k) or INNER / LEFT Join (for suppression tables above 1k rows). Note, and the field that I will suppress will be the index in the large base table, but the suppression table will not. Thoughts?

Thanks in advance for any comments and / or advice.

Answer 1

Assuming TSQL stands for SQL Server, have you seen this link regarding comparing NOT IN, NOT EXISTS and LEFT JOIN NULL ? Thus, while the columns being compared cannot be NULL, NOT IN and NOT EXISTS more efficient than LEFT JOIN/IS NULL ...

Something to keep in mind the difference between IN and EXISTS - EXISTS is a logical operator and returns true when the criteria are first executed. Although you see a correlated subquery in the syntax, EXISTS performed better than IN ...

In addition, IN and EXISTS only check for a comparison of values. This means that there is no duplication of entries that you find when you connect ...

It really depends, so if you really find what works best, you will have to test and compare what query plans do ...

Answer 2

It doesn’t matter which method you use, if there is no index in the table in which you apply the filter or join, the system will scan the table.

RE: Exists

It is not necessary that the system performs a subquery for all 1.4 million rows. SQL Server is smart enough to run the Exists internal query and then evaluate it against the main query. In some cases, Exists may perform equal to or better than Join.