Sql-server – Check for duplicates in column and determine which row to keep

I'm not 100% sure what you're trying to achieve, but would something like this solve if for you? (If so, I'm sure there's a more effective method as well).

SELECT 
BV.Title, 
BV2.Title 
FROM dbo.BaseValues AS BV
CROSS APPLY dbo.BaseValues AS BV2 
WHERE bv.Title <> BV2.Title
AND BV.ID <= BV2.ID  


SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV2.Title <> BV3.Title
    AND BV1.ID <= BV2.ID
    AND BV2.ID <= BV3.ID


    SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3,
    BV4.Title AS T4
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  CROSS JOIN BaseValues BV4
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV1.Title <> BV4.Title
    AND BV2.Title <> BV3.Title
    AND BV2.Title <> BV4.Title
    AND BV3.Title <> BV4.Title
    AND BV1.ID <= BV2.ID
    AND BV2.ID <= BV3.ID
    AND BV3.ID <= BV4.ID

In a world where the query optimizer considered all possible join orders, and contained all possible logical transformations, the syntax we use for our queries would not matter at all.

As it is, the optimizer generally uses heuristics to pick an initial join order and explores a number of join order rewrites from there. It does this to avoid excessive compilation time and resource usage. It doesn't take all that many joins for the number of possible combinations to become unreasonable to explore exhaustively.

To take an extreme example, 42 joins are enough to generate more alternatives than there are atoms in the observable universe. More realistically, even 7 tables are enough to produce 665,280 alternatives. Although this is not a mind-boggling number, it would still take very significant time (and memory) to explore those alternatives completely.

Although the heuristics are largely based on the type of join (inner, outer, cross...) and cardinality estimates, the textual order of the query can also have an impact. Sometimes, this is an optimizer limitation - NOT EXISTS clauses are not reordered, and outer join reordering is very limited. Even with simple inner joins, the interaction between textual order, initial join order heuristics, and optimizer internals can be difficult to predict with certainty.

To take an example using the AdventureWorks sample database, I can write a query using the a common syntax form as:

SELECT
    P.Name,
    PS.Name,
    SUM(TH.Quantity),
    SUM(INV.Quantity)
FROM Production.Product AS P
JOIN Production.ProductSubcategory AS PS
    ON PS.ProductSubcategoryID = P.ProductSubcategoryID
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
JOIN Production.ProductInventory AS INV
    ON INV.ProductID = P.ProductID
GROUP BY
    P.ProductID,
    P.Name,
    PS.ProductSubcategoryID,
    PS.Name;

Before cost-based optimization, the logical query tree looks like this (note the join order is not the same as the written order):

I can (carefully) rewrite the query to use 'nested' syntax:

SELECT
    P.Name,
    PS.Name,
    SUM(TH.Quantity),
    SUM(INV.Quantity)
FROM Production.ProductSubcategory AS PS
JOIN Production.Product AS P
JOIN Production.TransactionHistory AS TH
JOIN Production.ProductInventory AS INV
    ON INV.ProductID = TH.ProductID
    ON TH.ProductID = P.ProductID
    ON P.ProductSubcategoryID = PS.ProductSubcategoryID
GROUP BY
    P.ProductID,
    P.Name,
    PS.ProductSubcategoryID,
    PS.Name;

In which case the logical tree at the same point is:

The two different syntaxes produce a different initial join order in this case. After cost-based optimization, both produce the same output plan shape:

There are detailed differences between the two plans, with the 'nested' syntax producing a plan with a somewhat lower estimated cost:

The two inputs took a slightly different path through the optimizer, so it isn't all that surprising there are slight differences.

In general, using different syntax will sometimes (definitely not always!) produce different plan results. There is no broad correlation between one syntax and better plans. Most people write and maintain queries using something like the non-nested join syntax, so it often makes practical sense to use that.

To summarize, my advice is to write queries using whichever syntax seems most natural (and maintainable!) to you and your peers. If you get a better plan for a specific query using a particular syntax, by all means use it - but be sure to test that you still get the better plan whenever you patch or upgrade SQL Server :)