{"id":1484,"date":"2012-07-09T21:57:43","date_gmt":"2012-07-10T02:57:43","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?page_id=1484"},"modified":"2012-07-10T02:14:47","modified_gmt":"2012-07-10T07:14:47","slug":"oracle-sql-query-rewrite-not-exists-to-not-in-hash-anti-join","status":"publish","type":"page","link":"https:\/\/www.dpriver.com\/blog\/list-of-demos-illustrate-how-to-use-general-sql-parser\/oracle-sql-query-rewrite\/oracle-sql-query-rewrite-not-exists-to-not-in-hash-anti-join\/","title":{"rendered":"Oracle SQL Query rewrite: NOT EXISTS to NOT IN hash anti-join"},"content":{"rendered":"

NOT EXISTS to NOT IN hash anti-join<\/p>\n

If SQL statement has NOT EXISTS sub-query then we can re-write it using NOT IN hash anti-join.<\/p>\n

This is an article of Oracle SQL Query rewrite serials<\/a>.<\/p>\n

Take this sql for example:<\/p>\n

    \r\nSELECT sum(salary)\r\nFROM   emp, salary\r\nWHERE  emp.emp_id = salary.emp_id\r\nAND    NOT EXISTS (SELECT 1\r\n                   FROM   dept\r\n                   WHERE  dept.location = 'SEOUL'\r\n                   AND    emp.dept_no   = dept.dept_no\r\n                   ) ;\r\n<\/pre>\nSQL will be translated into:<\/p>\n\r\nSELECT SUM(salary)\r\nFROM   emp,\r\n       salary\r\nWHERE  emp.emp_id = salary.emp_id\r\n       AND emp.dept_no IS NOT NULL\r\n       AND emp.dept_no NOT IN (SELECT \/*+ HASH_AJ *\/ dept.dept_no\r\n                               FROM   dept\r\n                               WHERE  dept.location = 'SEOUL'\r\n                                      AND dept.dept_no IS NOT NULL) \r\n<\/pre>\nDownload this demo: C# version<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"NOT EXISTS to NOT IN hash anti-join If SQL statement has NOT EXISTS sub-query then we can re-write it using NOT IN hash anti-join. This is an article of Oracle SQL Query rewrite serials. Take this sql for example: SELECT sum(salary) FROM emp, salary WHERE emp.emp_id = salary.emp_id AND NOT EXISTS (SELECT 1 FROM dept WHERE dept.location = ‘SEOUL’ AND emp.dept_no = dept.dept_no ) ; SQL will be translated into: SELECT SUM(salary) FROM emp, salary WHERE emp.emp_id = salary.emp_id AND emp.dept_no IS NOT NULL AND emp.dept_no NOT IN (SELECT \/*+ HASH_AJ *\/ dept.dept_no FROM dept WHERE dept.location = ‘SEOUL’ AND\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1445,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"gsp_feature_page_tt.php","meta":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nHelp you to make better use of General SQL Parser<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n