TSQL "where - 101"

  • It's a nice question that might confuse some newbies. It's important to study the logical operators and the results given.

    I'd love to see in the explanation that the correct answer is translated as follows:

    SELECT EmployeeName

    FROM #QOTD

    WHERE NOT (EmployeeName = 'steve' OR EmployeeName = 'grant');

    Which is very similar to one of the options.

    If we want to remove the parenthesis, the operators should change.

    SELECT EmployeeName

    FROM #QOTD

    WHERE NOT EmployeeName = 'steve'

    AND NOT EmployeeName = 'grant';

    It's all fun and games until we find an employee with a NULL value in its name.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I appreciated the little nod to De Morgan's Laws 🙂

  • Stuart Davies (11/12/2014)


    SQLRNNR (11/12/2014)


    hjp (11/12/2014)


    SQLRNNR (11/12/2014)


    Impossible. There are multiple correct answers.

    JK

    Ducks and runs - snigger.:-D:-D:-D:hehe:

    Like... what?? If you don't present your case and argue why you think so, then why bother writing a comment here?

    Hint in the JK and the smileys.

    It was a Joke.

    Phew - that's a relief - I didn't know if I should respond to it or not and didn't know how to put my argument! 😉

    ROFL

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • super easy but completely ran on auto pilot with a list of names by taking out duplicates.

  • Without a good 'reason' for the table, the id column by itself is not enough to say that there are 2 different "Brian" employees. the id might be refering to something as silly as a key assigned to an employee, so Brian might be one person with keys 8 and 9.

    The sample did not include the id column in the result so there is now way to notice the difference between the two "Brian" rows, and I tend to expect that duplicates are bad.

    ---> I tend to supply this logic to people every time someone gives me duplicate data <----

    I tend to write the code when analysing tables from some unknown system.

    [font="Courier New"]

    with sourcedata as (

    select potentialDuplCol as dupl_potentialDuplCol from sometable group by potentialDuplCol having count(*) > 1

    )

    select * from sourcedata

    inner join sometable on potentialDuplCol = dupl_potentialDuplCol

    [/font]

    This tends to quickly show why a column which was expected to a unique key, isn't a unique key.

  • Great question, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I am always surprised that when the question and answers hint that the database is not case sensitive there is no mention about this. All our databases are case sensitive and therefore I notice this most of the time. Would it not be better practice to code expecting this to be the case? When you are looking for specific entries (or exclude some) code these as if the entries are case sensitive.

    In this example there are multiple entries that are the same (Brian) without adding an ID of such, we are always arguing that this is bad practice. Would it not be better that we try to make all questions of a high standard unless we want to make a point that requires those criteria?

    Just my 2 cents

  • Basically a good and interesting question, but Steffen Mantz-204830's point is a valid criticism. I don't think that the omission of ID from the select list would lead anyone to get the wrong answer, though - but if there had been a "none of teh above" otion that minor flaw would have been a critical one.

    Tom

  • Thanks for the question.

  • Luis Cazares (11/12/2014)


    It's all fun and games until we find an employee with a NULL value in its name.

    A NULL in the QOTD table would in this case not be a problem. The problems you are thinking about occur when there is a NULL in the IN list.

    Which is why I only use IN with a list of constants, never ever with a subquery - not even when I know there will not be NULL values there. Too subject to future change. And every query with [NOT] IN + subquery can always be rewritten to use [NOT] EXISTS.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • First of all, the column, ID, is accessed in the subsequent SQL as id, so

    that it will fail. Later, the names are capitalized, and the query will actually

    return all the names, since "grant" and "steve" have no capital, they also

    qualify for the "not in" statement. The correct answer is "none".

  • Hugo Kornelis (11/13/2014)


    Luis Cazares (11/12/2014)


    It's all fun and games until we find an employee with a NULL value in its name.

    A NULL in the QOTD table would in this case not be a problem. The problems you are thinking about occur when there is a NULL in the IN list.

    Which is why I only use IN with a list of constants, never ever with a subquery - not even when I know there will not be NULL values there. Too subject to future change. And every query with [NOT] IN + subquery can always be rewritten to use [NOT] EXISTS.

    You're right, but even if the NULL is in the table and not in the list, the NULL value won't show up. I hope that most people are aware of that, but it's one of the problems people face when they start working with SQL and NULLs.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • +1

  • Nice question

Viewing 14 posts - 31 through 43 (of 43 total)

You must be logged in to reply to this topic. Login to reply