python + sqlite3: using IS NOT in a join query

Question

This works the way I expect (c is a cursor)

    c.execute('''SELECT e1.file, e1.sourceUrl, e1.rev 
        FROM externals1 AS e1
        LEFT JOIN externals as e0
        ON (e1.file = e0.file)
        AND (e1.rev <> e0.rev)
    ''')

but I get an error here:

    c.execute('''SELECT e1.file, e1.sourceUrl, e1.rev 
        FROM externals1 AS e1
        LEFT JOIN externals as e0
        ON (e1.file = e0.file)
        AND (e1.rev IS NOT e0.rev)
    ''')

with the message

 sqlite3.OperationalError: near "e0": syntax error

What's going on? I want to handle the case where e0.rev is NULL.

Ken White · Accepted Answer · 2011-03-02 19:57:05Z

2

SELECT e1.file, e1.sourceUrl, e1.rev 
    FROM externals1 AS e1
    LEFT JOIN externals as e0
    ON (e1.file = e0.file)
    AND (e1.rev IS NOT NULL)

NULL means 'unassigned or unknown value', and as such can never be equal or not equal to something else. (How can something unknown or unassigned be compared to something else unknown or unassigned?) Therefore, you can't use <> or = to test them; you have to use the special operators IS/IS NOT instead.

You can also use

NOT (e1.rev = e0.rev)

if you're wanting to test for non-equality.

edited Mar 2, 2011 at 19:57

answered Mar 2, 2011 at 19:40

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user166390 Over a year ago

Note (as to why this syntax is valid): The constructs are operand IS NULL and operand IS NOT NULL. However, this isn't the same as query #1?

Ken White Over a year ago

@pst: I'm not sure what you're asking. IS NULL/IS NOT NULL isn't the same as the <> in the OP's first query at all. I'll add an explanation about the operands for NULL.

user166390 Over a year ago

@Ken White I was/am confused about the question -- I'm not sure what the operator was looking for, but this answer is accepted so it must have been desired :P

Jason S Over a year ago

I was confusing the "COL1 IS NOT COL2" idiom with the "COL1 IS NOT NULL" in a select/join.

Jason S Over a year ago

...so this answer made me realize I wanted something slightly different than what I asked for (namely some of the tests should have been in the WHERE clause rather than the JOIN clause)

|

OMG Ponies · Accepted Answer · 2011-03-02 19:39:24Z

2

!= is ANSI for not equals, what you posted in the second example is invalid SQL for any database I know of. You could try:

   SELECT e1.file, e1.sourceUrl, e1.rev 
     FROM externals1 AS e1
LEFT JOIN externals as e0 ON e0.file = e1.file
                         AND e1.rev NOT IN (e0.rev)

answered Mar 2, 2011 at 19:39

OMG Ponies

334k85 gold badges536 silver badges509 bronze badges

Comments

score 0 · Accepted Answer · 2011-03-02 20:23:53Z

0

SELECT e1.file, e1.sourceUrl, e1.rev 
FROM externals1 AS e1
LEFT JOIN externals as e0
  ON e0.file = e1.file
  AND e1.rev <> e0.rev
  AND e1.rev IS NOT NULL

The constructs are operand IS NULL and operand IS NOT NULL (there is only one operand to either the IS NUll/IS NOT NULL operators!) -- I imagine the NULL-check in addition to a 'not' is desired but it's hard to tell from the question.

Remember, x <> y is always true when either (or both) x or y are NULL. (At least for an SQL89-compliant engine.)

Happy SQL'ing.

Addition: The Wikipedia article on NULL is an interesting read, in general. It talks about some of the base NULL constructs (which may or may not entirely apply to a particular DB...)

edited Mar 2, 2011 at 20:23

answered Mar 2, 2011 at 19:49

user166390

Collectives™ on Stack Overflow

python + sqlite3: using IS NOT in a join query

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related