Home

Fix for EXISTS vs NOT EXISTS as a sql query

April 16, 2009 - 7:39am — burningdog
  1. # I have 3 vehicle nodes: 3, 26, 28. The following query should return ONLY nid 3, but it returns 3, 26, 28.
  2.  
  3. SELECT node.nid AS nid
  4. FROM node
  5. WHERE (node.STATUS <> 0) AND (node.type IN ('vehicle'))
  6. AND EXISTS (
  7. SELECT * FROM node WHERE nid = 3
  8. )
  9.  
  10. # the following query should return 26 and 28, but it returns nothing (note: changed EXISTS to NOT EXISTS)
  11.  
  12. SELECT node.nid AS nid
  13. FROM node
  14. WHERE (node.STATUS <> 0) AND (node.type IN ('vehicle'))
  15. AND NOT EXISTS (
  16. SELECT * FROM node WHERE nid = 3
  17. )

Submit Fix

Any tags you'd like to associate with your code, delimitered by commas (example: Views, CCK, Module, etc).
Select the syntax highlighting mode to use.