why one symbol not work for MS SQL?

Question

I have some query:

delete from swi
    where co||na in (
        select co||na
        from swi
        where co||na not in (
            select co||na
            from swi_tmp
        )
    )

and it works on ORACLE DB. But in MS SQL this does not work. How I can get this query to work?

Are you trying to concatenate two columns or you really have a literal `||` as part of the column name? — Álvaro González, Dec 01 '17 at 11:24
@АлександрПлющев correct at all where you find `co||na` with `[co||na]` — Yogesh Sharma, Dec 01 '17 at 11:26
Note that in Oracle `where (co, na) in (select co, na ...)` would be better and probably more efficient as well. — , Dec 01 '17 at 11:38

score 3 · Answer 1 · answered Dec 01 '17 at 11:36

3

Because SQL Server uses + for string concatenation not the || operator that is defined in the SQL standard.

You probably need co+na instead of co||na

answered Dec 01 '17 at 11:36

Bingo. But they should use a different approach. This string concatenation approach is pretty horrible and may not behave as they want. e.g. `('abc', 'def')` will be treated the same as `('abcd', 'ef')` – Martin Smith Dec 01 '17 at 12:17
@MartinSmith: I completely agree. In Oracle `where (co, na) in (select co, na ...)` would be a much better choice, but that isn't possible in SQL Server if I'm not mistaken. – Dec 01 '17 at 12:18
No it isn't possible in SQL Server you would need to use `EXISTS` / `NOT EXISTS` rather than `IN`/`NOT IN` here – Martin Smith Dec 01 '17 at 12:19

Dan Guzman · Accepted Answer · 2017-12-01T12:23:33.797

3

The main problem is string concatenation operator as called out in the other answers and comments. However, the concatenation method prevents a sargable expression so indexes on the columns cannot be used efficiently. This is very bad for concurrency since the DELETE will need to perform a full scan, touching all rows in the table instead of only those that are needed.

You could refactor the query to use EXISTS to allow efficient index use and specify table aliases to avoid ambiguity:

DELETE FROM swi
WHERE EXISTS( 
        SELECT 1
        FROM swi AS a
        WHERE a.co = swi.co
            AND a.na = swi.na
            AND NOT EXISTS (
            SELECT 1
            FROM swi_tmp AS b
            WHERE 
                b.co = swi.co
                AND b.na = swi.na
        )
    );

And better, remove the redundant EXISTS, which will be true for all rows anyway (except NULL values):

DELETE FROM swi
WHERE NOT EXISTS (
    SELECT 1
    FROM swi_tmp
    WHERE 
        swi_tmp.co = swi.co
        AND swi_tmp.na = swi.na
        );

Note that saragable expressions will improve performance in all RDBMS products, not just SQL Server, and are a best practice.

edited Dec 01 '17 at 12:23

answered Dec 01 '17 at 12:19

Dan Guzman

43,250
3
46
71

That is also the better solution in Oracle – Dec 01 '17 at 12:20
I'll improve my answer with that info. – Dan Guzman Dec 01 '17 at 12:21
@АлександрПлющев, I don't understand your question. My answer includes the only query in your question. I think it will work with `Oracle` too. – Dan Guzman Dec 01 '17 at 13:01
@DanGuzman I have a similar question with other query and I need a help((( – noxi Dec 02 '17 at 17:25
@АлександрПлющев, what other query? There is only one in your question. – Dan Guzman Dec 02 '17 at 17:35
@DanGuzman Other query in other question)) can you help me if I put here link to other question on stackoverflow? – noxi Dec 02 '17 at 17:58
I'll take a look if you provide the link to your question. – Dan Guzman Dec 02 '17 at 18:04
@DanGuzman https://stackoverflow.com/questions/47594139/rewrite-query-for-sql-server-from-oracle and I want ask, really create one query for postregressql, oracle, and mssql? – noxi Dec 02 '17 at 18:08

score -1 · Answer 3 · answered Dec 01 '17 at 11:24

-1

As a general rule, don't use special characters (that includes white space) and reserved words when naming objects. If you do, like @Yogesh stated, you have to quote them using brackets. Names like the following would have to be quoted [co||an], [Order], [Total Value] because they contain special characters or are a reserved word (order).

answered Dec 01 '17 at 11:24

Thom A

88,727
11
45
75

1

`||` is not a "special character". It's the string concatenation operator in SQL. – Dec 01 '17 at 11:38
No `+` is used in T-SQL to concatenate. `SELECT 'Hello' + ' ' + 'Goodbye';` `||` in SQL Server is simply a double pipe, it has no other meaning. – Thom A Dec 01 '17 at 11:44
1

In "SQL" (the standard for query languages) `||` **is** the operator for string concatenation. And it has been that for nearly 30 years now. SQL Server (or actually Sybase back when they created SQL Server) chose to ignore that standard and uses `+` in T-SQL. – Dec 01 '17 at 11:46
@a_horse_with_no_name This is a SQL-server and T-SQL question. hence the answers you provide need to be for T-SQL, not other (SQL) languages. SAying something works in Oracle or MySQL doesn't mean it'll work in T-SQL, for the same reason a T-SQL solution might not work in Oracle or MySQL, etc. The lanugages **are** different (despite there being standards that none of them completely follow). – Thom A Dec 01 '17 at 11:46
I answered for T-SQL - your answer assuming that there is a column that is named `co||an` is wrong because in Oracle the expression `co||an` means "`co` concatenated with `an`" - so there is one column named `co` and one named `an` – Dec 01 '17 at 11:49
You can't have. `co||an` is invalid syntax for T-SQL for the above reasons. The string concatenation character in T-SQL is `+`. Your answer is like bringing a C# answer to a VB.Net question. – Thom A Dec 01 '17 at 11:51
1

The query in the question is the original Oracle query. And to translate that to T-SQL you need to replace `||` with `+` there is no column wit the name `co||an` in the question – Dec 01 '17 at 11:53

why one symbol not work for MS SQL?

3 Answers3